Can Rows Be Combined in Excel?

[Kevin Neufeld]

Hello - looking for some help. The below current report shows (2) transport orders that are representing (1) load. The data is shown in 2 lines as the delivery city for each is different. What I am looking for is a method to take that data and make one line. I have shown an example of the desired output which would put the 2nd delivery city out to the right and have all data in one row.

Thanks in advance

Current Report
Date	Transport ID #	Load ID #	Origin City	Delivery City
1/6/20	a1	1	Granite City	Taylorville
1/6/20	a2	1	Granite City	Assumption
Desired Output
Date	Load ID #	Origin City	Delivery City 1	Delivery City 2
1/6/20	1	Granite City	Taylorville	Assumption

 

[sandy666]

with Power Query

Date	Transport ID #	Load ID #	Origin City	Delivery City		Date	Load ID #	Origin City	Delivery City.1	Delivery City.2
01/06/2020	a1	1	Granite City	Taylorville		01/06/2020	1	Granite City	Taylorville	Assumption
01/06/2020	a2	1	Granite City	Assumption

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Group = Table.Group(Type, {"Date", "Load ID #", "Origin City"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Delivery City", each [Count][Delivery City]),
    Extract = Table.TransformColumns(List, {"Delivery City", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Delivery City", Splitter.SplitTextByAnyDelimiter({","}))
in
    Split