Properly unpivitoing columns for a dataset

[thmonte]

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.

[v-eachen-msft]

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.
 

[thmonte · Accepted 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..

[v-eachen-msft]

Hi @thmonte ,

I don't think there is any difference in performance. You can pick a method that is easier for you to understand.

[PattemManohar]

@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 ?

[thmonte]

It's constant. In my case it's always 8 columns for each