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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Combining rows based on duplicate column value

I have a merged table in the Power Query Editor that looks like this:

 

FruitAttributeValue
AppleTextureCrunchy
AppleColorRed
AppleColorGreen
BananaTextureSoft
BananaColorYellow
GrapeTextureCrunchy
GrapeTextureChewy
GrapeColorPurple

 

I'd like to combine the rows with the same Attribute value in such a way that the Attribute value remains unchanged and the Value values become a combined, comma-separated value:

 

FruitAttributeValue
AppleTextureCrunchy
AppleColorRed, Green
BananaTextureSoft
BananaColorYellow
GrapeTextureCrunchy, Chewy
GrapeColorPurple

 

Is there a way to do this in the Power Query Editor? Any help is appreciated. Thanks.

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

There isn't a very (GUI based) quick way of doing this but it is not very hard to accomplish. The following code is the M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEKSa0oKS0CsZyLSvOSMyqVYnUQss75OflFQDooNQWruHtRamoeWMYpMQ8IUQwMzk8rQZWDaYtMzcnJLwfLuRclFuB2BxbZjNRyVDmYoQGlRSDHxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fruit = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit", "Attribute"}, {{"Count", each Text.Combine([Value], ", "), type text}})
in
    #"Grouped Rows"

Look at the line starting with #"Grouped Rows", it takes your original table, groups it by the two columns and the values corresponding per group are a list. That list can be input of the Text.Combine function. 

Let me know if this helps you out 🙂 You can copy paste the entire code into an empty query by opening the 'Advanced Editor'.

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
JarroVGIT
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

There isn't a very (GUI based) quick way of doing this but it is not very hard to accomplish. The following code is the M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEKSa0oKS0CsZyLSvOSMyqVYnUQss75OflFQDooNQWruHtRamoeWMYpMQ8IUQwMzk8rQZWDaYtMzcnJLwfLuRclFuB2BxbZjNRyVDmYoQGlRSDHxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fruit = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit", "Attribute"}, {{"Count", each Text.Combine([Value], ", "), type text}})
in
    #"Grouped Rows"

Look at the line starting with #"Grouped Rows", it takes your original table, groups it by the two columns and the values corresponding per group are a list. That list can be input of the Text.Combine function. 

Let me know if this helps you out 🙂 You can copy paste the entire code into an empty query by opening the 'Advanced Editor'.

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Works perfectly. Thanks for the help!

Hi @Anonymous 

 

@JarroVGIT has the right answer for you, but you can use the GUI to give you a bit of a head start.

CombineRowValues.png

 

As a further step, you might also pivot on the attribute column so that Texture and Color become separate columns instead of separate rows and there's only one row per fruit. 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors