Forum Discussion
Split values from different rows of a same column into a single row
Hi,
my problem is simple but I don't find any good solution on internet.
Below is what I have :
| column1.id | column2.id | Column3 |
| 2850 | 718012 | Date1 |
| 2850 | 718012 | Value1 |
| 2852 | 880592 | Date2 |
| 2852 | 880592 | Value2 |
And here is what I want :
| Column1.id | Column2.id | Dates | Values |
| 2850 | 718012 | Date1 | Value1 |
| 2852 | 880592 | Date2 | Value2 |
Any suggestion will be appreciated.
Thanks in advance,
Cado
Cado_one
Paste below code in a blank query in the advanced editor.
The idea is summarize by the 1st two columns and sum the 3rd column then replace the List.Sum() with Text.Combine([Column3],",")let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIwNVDSUTI3tDAwNAIyXBJLUg2VYnUwZcISc0oRUiARCwsDU0uYJiOsMmBNQKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1.id = _t, column2.id = _t, Column3 = _t]), #"Grouped Rows" = Table.Group(Source, {"column1.id", "column2.id"}, {{"Count", each Text.Combine([Column3],","), type nullable text}}) in #"Grouped Rows"________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
6 Replies
Hi, Cado_one
let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WSs7PKc3NM9TLTFGyMrIwNdCBihiBRcwNLQwMjXSUnMFixkpWSi6JJamGSrU6pOsMS8wpxa7VCFWrhYWBqSWGpUZk6QRbCtQaCwA=",BinaryEncoding.Base64),Compression.Deflate))), group = Table.Group(Source, {"column1.id", "column2.id"}, {"Foo", each Record.FromList([Column3],{"Dates", "Values"}) }), expd = Table.ExpandRecordColumn(group, "Foo", {"Dates", "Values"}) in expdIf my code solves your problem, mark it as a solution
Cado_one
Paste below code in a blank query in the advanced editor.
The idea is summarize by the 1st two columns and sum the 3rd column then replace the List.Sum() with Text.Combine([Column3],",")let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIwNVDSUTI3tDAwNAIyXBJLUg2VYnUwZcISc0oRUiARCwsDU0uYJiOsMmBNQKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column1.id = _t, column2.id = _t, Column3 = _t]), #"Grouped Rows" = Table.Group(Source, {"column1.id", "column2.id"}, {{"Count", each Text.Combine([Column3],","), type nullable text}}) in #"Grouped Rows"________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
Thanks you for your responses !
ziying35 your code give me an error : The column 'Column1.id' of the table wasn't found.
Fowmy your code give me an error : 4 keys were specified, but 3 values were provided.
In order to understand the first operation and to be able to do it again by myself, could you please show/tell me of to do the trick manually ?
Cado
