Combining rows based on duplicate column value

[Anonymous]

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

Fruit	Attribute	Value
Apple	Texture	Crunchy
Apple	Color	Red
Apple	Color	Green
Banana	Texture	Soft
Banana	Color	Yellow
Grape	Texture	Crunchy
Grape	Texture	Chewy
Grape	Color	Purple

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:

Fruit	Attribute	Value
Apple	Texture	Crunchy
Apple	Color	Red, Green
Banana	Texture	Soft
Banana	Color	Yellow
Grape	Texture	Crunchy, Chewy
Grape	Color	Purple

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

[JarroVGIT · Accepted solution]

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! 🙂

[Anonymous]

Works perfectly. Thanks for the help!

[irobba]

Hi @Anonymous 

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

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.