put records in different corresponding columns

alipezu

Board Regular
Joined
Oct 18, 2016
Messages
51
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,699
something like this?
Bus Driver IDBus Driver NameBus No.Trip Name.1Bus Assistant ID.1Bus Assistant Name.1Trip Name.2Bus Assistant ID.2Bus Assistant Name.2
25Reginald Hilton B2685Jood24159Eloise Smelley 47717Margit Jacobs
15Tanner Straughan B4242Jood26851Shona Harned
21Antwan Darland B4275Jood36258Marisela Boster Tomo35826Adrianna Gott
29Benton Yann B2733Alya36679Stephanie Keplin
45Vicente Merola B2734Alya43655Dayle Gearheart Refa28534Shemeka Campion
24Jonathan Wert B4539Refa39191Lili Wiemann
30Hosea Asmus B4649NeoNeon41772Karla Fernando
26Benton Yann B2733Waol24986Eula Aronson
 

alipezu

Board Regular
Joined
Oct 18, 2016
Messages
51
something like this?
Bus Driver IDBus Driver NameBus No.Trip Name.1Bus Assistant ID.1Bus Assistant Name.1Trip Name.2Bus Assistant ID.2Bus Assistant Name.2
25Reginald Hilton B2685Jood24159Eloise Smelley 47717Margit Jacobs
15Tanner Straughan B4242Jood26851Shona Harned
21Antwan Darland B4275Jood36258Marisela Boster Tomo35826Adrianna Gott
29Benton Yann B2733Alya36679Stephanie Keplin
45Vicente Merola B2734Alya43655Dayle Gearheart Refa28534Shemeka Campion
24Jonathan Wert B4539Refa39191Lili Wiemann
30Hosea Asmus B4649NeoNeon41772Karla Fernando
26Benton Yann B2733Waol24986Eula Aronson
Looks something similar but some data in Trip2 is still missing
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,699
1. why there is no ID 26 (Benton Yann) ?
2 Could you show logic for trip 2 ?
 

alipezu

Board Regular
Joined
Oct 18, 2016
Messages
51

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2015
Messages
6,699
Like this?
Bus Driver IDBus Driver NameBus No.Trip 1Assist ID 1Assist Name 1Trip 2Assist ID 2Assist Name 2
15Tanner Straughan B4242Jood26851Shona Harned
21Antwan Darland B4275Jood36258Marisela Boster Tomo35826Adrianna Gott
24Jonathan Wert B4539Refa39191Lili Wiemann
25Reginald Hilton B2685Jood24159Eloise Smelley Jood47717Margit Jacobs
29Benton Yann B2733Waol24986Eula Aronson Alya36679Stephanie Keplin
30Hosea Asmus B4649NeoNeon41772Karla Fernando
45Vicente Merola B2734Refa28534Shemeka Campion Alya43655Dayle Gearheart
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,699

ADVERTISEMENT

Headers correction
Bus Driver IDBus Driver NameBus No.Trip Name 1Bus Assistant ID 1Bus Assistant Name 1Trip Name 2Bus Assistant ID 2Bus Assistant Name 2
15Tanner Straughan B4242Jood26851Shona Harned
21Antwan Darland B4275Jood36258Marisela Boster Tomo35826Adrianna Gott
24Jonathan Wert B4539Refa39191Lili Wiemann
25Reginald Hilton B2685Jood24159Eloise Smelley Jood47717Margit Jacobs
29Benton Yann B2733Waol24986Eula Aronson Alya36679Stephanie Keplin
30Hosea Asmus B4649NeoNeon41772Karla Fernando
45Vicente Merola B2734Refa28534Shemeka Campion Alya43655Dayle Gearheart
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,699
ops, I forgot to post M
Power Query:
// 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

Board Regular
Joined
Oct 18, 2016
Messages
51
ops, I forgot to post M
Power Query:
// 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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,740
Messages
5,524,557
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top