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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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
 
Upvote 0
1. why there is no ID 26 (Benton Yann) ?
2 Could you show logic for trip 2 ?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top