Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to 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".
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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |