Forum Discussion
Power Query: How do I convert multiple rows to comma separated values, according to their IDs?
Hi everyone!
I have the following dataset:
| ID | Colour |
| 1 | Red |
| 1 | Blue |
| 1 | Green |
| 1 | Yellow |
| 2 | Red |
| 2 | Blue |
| 3 | Green |
How can I transform it to the following, but it has to be in Power Query:
| ID | Colours |
| 1 | Red, Blue, Green, Yellow |
| 2 | Red, Blue |
| 3 | Green |
Thank you!
Hi Anonymous
This article explains it. You have to group it and then tweak the code little bit to join text rather than sum.
Thanks,
thingsclump
5 Replies
- Thingsclump
Resolver V
Hi Anonymous
This article explains it. You have to group it and then tweak the code little bit to join text rather than sum.
Thanks,
thingsclump
That's awesome. Thank you Thingsclump- It works. Thank you!
It works. Thank you!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKTVGK1YGwnXJKU+Ec96LU1Dw4LzI1Jye/HMw1QtJlhKzLGKErFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Colour = _t]),
#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each _[Colour], type table [ID=nullable text, Colour=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count],",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"})
in
#"Removed Columns"
