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.
If I have a dataset that looks like the following:
person | Attrib1 | Attrib2 | Attribute3 | Attrib1Value | Attrib2Value | Attrib3Value |
John | Eyes | Fingers | Nose | 2 | 5 | 1 |
Lou | Eyes | Fingers | Nose | 2 | 0 | 1 |
How do I turn it into something like this
name | attrib | amount |
John | Eyes | 2 |
John | Fingers | 5 |
John | Nose | 1 |
Lou | Eyes | 2 |
Lou | Nose | 1 |
Typically when i research this issue the resolution is to always create multiple queries for each column pair and then append them to each other but to me thats the least pefromance friendly way as I have to load thousands of rows on a refresh and all these queires fill up my editor.
Solved! Go to Solution.
@v-eachen-msft thank you for the response but i acheived the same thing using this other solution found here:
https://community.powerbi.com/t5/Desktop/Unpivot-Multiple-Coumns/td-p/442606
However, I'm not sure which is more performance friendly..
Hi @thmonte ,
You can follow these steps:
1. You need to replace "Attribute3" with "Attrib3".
2. Unpivot "Attrib1","Attrib2","Attrib3" columns.
3. After it, unpivot "Attrib1Value","Attrib2Value","Attrib3Value" columns.
4. Then, replace "Value" with "".
5. Open Advanced Editor, add an m query:
#"Remove Rows" = Table.SelectRows(#"Replaced Value",each ([Attribute]=[Attribute.1]) and ([Value.1]<>"0"))
6. Remove "Attribute" and "Attribute1" columns.
Here is the result.
You can download my test file from the link.
@v-eachen-msft thank you for the response but i acheived the same thing using this other solution found here:
https://community.powerbi.com/t5/Desktop/Unpivot-Multiple-Coumns/td-p/442606
However, I'm not sure which is more performance friendly..
Hi @thmonte ,
I don't think there is any difference in performance. You can pick a method that is easier for you to understand.
@thmonte Could you please confirm - Is the number of attribute columns (in this example, which are three attributes) is constant or it will also change dynamically ?
Proud to be a PBI Community Champion
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |