Forum Discussion
Combining rows based on duplicate column value
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.
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! 🙂
3 Replies
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! 🙂
