Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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! 🙂
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |