Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
Thanks,
thingsclump
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"
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
It works. Thank you!