How to unpivot table with multiple nested columns?

[DCSupport]

Hi 

We have this data that needs to unpivot however it has another layer at the top. See the example below:

			Vendor 1			Vendor 2			Vendor 3
Req Key	Value 1	Calculated Value 1	Calculated Value 2	Total Value	Calculated Value 1	Calculated Value 2	Total Value	Calculated Value 1	Calculated Value 2	Total Value
R01	3	8	2	16	6	1	6	4	1	4
R02	2	6	1	6	4	1	4	5	1	5
R03	2	6	1	6	5	1	5	6	1	6

Each vendor will have columns for Calculated Value 1, Calculated Value 2 and Total Value

How do we do this to unpivot this?

I'm appreciated your help.

Thanks

[v-juanli-msft]

Hi @DCSupport 

First, Promoted Headers to get the following

Rename columns by adding "Vendor 1", "Vendor 2","Vendor 3"

Then select "Req Key" and "Value 1" columns, "Unpivot other columns",

Next, Split columns by delemiter "_",

Finally, unpivot the Attribute.2 column.

You could find detailed steps in my pbix.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ImkeF]

Hi @DCSupport ,

if you number of vendors is dynamic, you might prefer this fully dynamic solution intead: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...

Please check out the enclosed file.