Forum Discussion
How to unpivot table with multiple nested columns?
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
2 Replies
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
MaggieCommunity 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.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-powerquery-in-excel/
Please check out the enclosed file.
