put records in different corresponding columns

[alipezu]

Hi All,
Please help me transfer data from Table1 into Table2 using Power Query (Get & Transform).
* Each driver is assigned a Bus. The driver can have either one trip or two. Also The driver has a Bus Assistant who helps him in a trip. But the assistant must have only one trip. I mean the Assitant is unique and will not be repeated more than one time while the driver along his bus can be repeated.
* If the Trip Sequence is 1, it should go in its corresponding column 1 and so for Trip 2.
Thanks.
Link to the file is here: Book113.xlsx

 

[sandy666]

something like this?

Bus Driver ID	Bus Driver Name	Bus No.	Trip Name.1	Bus Assistant ID.1	Bus Assistant Name.1	Trip Name.2	Bus Assistant ID.2	Bus Assistant Name.2
25	Reginald Hilton	B2685	Jood	24159	Eloise Smelley		47717	Margit Jacobs
15	Tanner Straughan	B4242	Jood	26851	Shona Harned
21	Antwan Darland	B4275	Jood	36258	Marisela Boster	Tomo	35826	Adrianna Gott
29	Benton Yann	B2733	Alya	36679	Stephanie Keplin
45	Vicente Merola	B2734	Alya	43655	Dayle Gearheart	Refa	28534	Shemeka Campion
24	Jonathan Wert	B4539	Refa	39191	Lili Wiemann
30	Hosea Asmus	B4649	NeoNeon	41772	Karla Fernando
26	Benton Yann	B2733	Waol	24986	Eula Aronson

 

[alipezu]

something like this?

Bus Driver ID	Bus Driver Name	Bus No.	Trip Name.1	Bus Assistant ID.1	Bus Assistant Name.1	Trip Name.2	Bus Assistant ID.2	Bus Assistant Name.2
25	Reginald Hilton	B2685	Jood	24159	Eloise Smelley		47717	Margit Jacobs
15	Tanner Straughan	B4242	Jood	26851	Shona Harned
21	Antwan Darland	B4275	Jood	36258	Marisela Boster	Tomo	35826	Adrianna Gott
29	Benton Yann	B2733	Alya	36679	Stephanie Keplin
45	Vicente Merola	B2734	Alya	43655	Dayle Gearheart	Refa	28534	Shemeka Campion
24	Jonathan Wert	B4539	Refa	39191	Lili Wiemann
30	Hosea Asmus	B4649	NeoNeon	41772	Karla Fernando
26	Benton Yann	B2733	Waol	24986	Eula Aronson

Looks something similar but some data in Trip2 is still missing

 

[sandy666]

1. why there is no ID 26 (Benton Yann) ?
2 Could you show logic for trip 2 ?

 

[alipezu]

1. why there is no ID 26 (Benton Yann) ?
2 Could you show logic for trip 2 ?

Sorry, Benton Yann has ID 29 rather than 26.
I think it would be better if we call them schools rather than trips. There are school Jood, Alya, Refa etc. some schools like Jood has two sections the girls section and the boys section, so there are two trips for this schools. One trips ends at 7 am and the other at 9 am.
e.g. Reginald Hilton has two trips every day, one at 7 am (boys section) and the other 9 am (girls section).
Tanner Straughan has only single trip in Jood School (girls section) which is at 9 am.
I think everything is clear now.

 

[sandy666]

Like this?

Bus Driver ID	Bus Driver Name	Bus No.	Trip 1	Assist ID 1	Assist Name 1	Trip 2	Assist ID 2	Assist Name 2
15	Tanner Straughan	B4242				Jood	26851	Shona Harned
21	Antwan Darland	B4275	Jood	36258	Marisela Boster	Tomo	35826	Adrianna Gott
24	Jonathan Wert	B4539	Refa	39191	Lili Wiemann
25	Reginald Hilton	B2685	Jood	24159	Eloise Smelley	Jood	47717	Margit Jacobs
29	Benton Yann	B2733	Waol	24986	Eula Aronson	Alya	36679	Stephanie Keplin
30	Hosea Asmus	B4649				NeoNeon	41772	Karla Fernando
45	Vicente Merola	B2734	Refa	28534	Shemeka Campion	Alya	43655	Dayle Gearheart

 

[sandy666]

Headers correction

Bus Driver ID	Bus Driver Name	Bus No.	Trip Name 1	Bus Assistant ID 1	Bus Assistant Name 1	Trip Name 2	Bus Assistant ID 2	Bus Assistant Name 2
15	Tanner Straughan	B4242				Jood	26851	Shona Harned
21	Antwan Darland	B4275	Jood	36258	Marisela Boster	Tomo	35826	Adrianna Gott
24	Jonathan Wert	B4539	Refa	39191	Lili Wiemann
25	Reginald Hilton	B2685	Jood	24159	Eloise Smelley	Jood	47717	Margit Jacobs
29	Benton Yann	B2733	Waol	24986	Eula Aronson	Alya	36679	Stephanie Keplin
30	Hosea Asmus	B4649				NeoNeon	41772	Karla Fernando
45	Vicente Merola	B2734	Refa	28534	Shemeka Campion	Alya	43655	Dayle Gearheart

 

[sandy666]

ops, I forgot to post M

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOSC = Table.Unpivot(Source, {"Trip Name", "Bus Assistant ID", "Bus Assistant Name"}, "Attribute", "Value"),
    CC = Table.CombineColumns(Table.TransformColumnTypes(UOSC, {{"Trip Sequence", type text}}, "en-GB"),{"Attribute", "Trip Sequence"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Pvt = Table.Pivot(CC, List.Distinct(CC[Merged]), "Merged", "Value")
in
    Pvt

 

[alipezu]

ops, I forgot to post M

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOSC = Table.Unpivot(Source, {"Trip Name", "Bus Assistant ID", "Bus Assistant Name"}, "Attribute", "Value"),
    CC = Table.CombineColumns(Table.TransformColumnTypes(UOSC, {{"Trip Sequence", type text}}, "en-GB"),{"Attribute", "Trip Sequence"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Pvt = Table.Pivot(CC, List.Distinct(CC[Merged]), "Merged", "Value")
in
    Pvt

Thank you sandy666 . It worked

 

[sandy666]

Thank you sandy666 . It worked

You are welcome