Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

Hi everyone!

 

I have the following dataset:

IDColour
1Red
1Blue
1Green
1Yellow
2Red
2Blue
3Green

 

How can I transform it to the following, but it has to be in Power Query:

 

IDColours
1Red, Blue, Green, Yellow
2Red, Blue
3Green


Thank you!

1 ACCEPTED SOLUTION
Thingsclump
Resolver V
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.

 

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 

View solution in original post

5 REPLIES 5
adityaD
Frequent Visitor

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
Resolver V
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.

 

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 

It works. Thank you!

It works. Thank you!
Anonymous
Not applicable


That's awesome. Thank you @Thingsclump 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.