Group ligns together and concatenate them in a specific order (Power Query)

[Anonymous]

Hi everyone,

I am having some trouble resolving this with power Query :

I have a list of customers, with name of cities where they went and with the order in which they went in each city (all customers don't go to the same cities and the order in which they go between cities is not always the same).

What I need Is a table where I could see the customer ID and a concatened column where I could see where the customers went in the right order.

For instance, this is what i have : 

Customer Id	City	Order
1	Los Angeles	1
1	New York	3
1	Paris	2
2	Los Angeles	1
2	New York	2
2	Miami	3
2	Londres	4
3	Toronto	1
3	Vancouver	2
3	Mexico	3
3	New York	4
4	Paris	1
4	Toronto	2

And this is what I need :

Customer Id	Cities
1	Los Angeles - Paris - New York
2	Los Angeles - New York - Miami - Londres
3	Toronto - Vancouver - Mexico
4	Paris-Toronto

Thank you !

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLJL1ZwzEtPzUktBvIMlWJ1IOJ+qeUKkflF2UCmMVwwILEoE6TMCCxihEO7Eap2hGLfzMTcTLiBEO15KUVgrSZgMWMgKyS/KD+vJB9uHEgsLDEvOb+0LLUIbh5I1De1IjM5H26gMaq9EBNNkJxtCBdB2AE0LRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Id" = _t, City = _t, Order = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Id", Int64.Type}, {"City", type text}, {"Order", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer Id", Order.Ascending}, {"Order", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer Id"}, {{"Rows", each _, type table [Customer Id=nullable number, City=nullable text, Order=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Rows][City]," - ")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Customer Id", "Custom"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".