Power Query: How do I convert multiple rows to comma separated values, according to their IDs?

[Anonymous]

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!

[adityaD]

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"

[Thingsclump · Accepted solution]

Hi @Anonymous 

This article explains it. You have to group it and then tweak the code little bit to join text rather than sum.

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/group-by-to-merge-texts-something-like-concatenatex-but-in-queries/

Thanks,

thingsclump

www.thingsclump.com 

[pavel_lazarov]

It works. Thank you!

[pavel_lazarov]

It works. Thank you!

[Anonymous]

That's awesome. Thank you @Thingsclump