Forum Discussion
Pivot table based on multiple values / rows to columns
Hi all,
I have the below dataset and I'd like to have it in a new format (2nd table).
I've already created this in Power Query by pivoting the table in 2 separate queries:
1) pivot based on Value_A
2) pivot based on Value_B
then
3) add a new column (e.g.: Type: which has value A in the first and B in the second table)
and finally
4) append the 2 queries into 1 new.
My issue is that it seems to be a bit time consuming especially when I have A,B,C,D,E... values too, do you have any idea how to replicate this in a more efficient way? I don't want to summarize the values or execute any type of modification.
Input:
| Country | Year | Value_A | Value_B |
| Country_1 | 2020 | 4 | 8 |
| Country_1 | 2021 | 0 | 0 |
| Country_1 | 2022 | 5 | 10 |
| Country_2 | 2020 | 9 | 3 |
| Country_2 | 2021 | 3 | 8 |
| Country_2 | 2022 | 3 | 4 |
| Country_3 | 2020 | 3 | 7 |
| Country_3 | 2021 | 2 | 8 |
| Country_3 | 2022 | 5 | 2 |
Result:
| Year | Country_1 | Country_2 | Country_3 | Type |
| 2020 | 4 | 9 | 3 | A |
| 2021 | 0 | 3 | 2 | A |
| 2022 | 5 | 3 | 5 | A |
| 2020 | 8 | 3 | 7 | B |
| 2021 | 0 | 8 | 8 | B |
| 2022 | 10 | 4 | 2 | B |
Thanks,
PDG
Hi P_D_G ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
4 Replies
Hi P_D_G ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
Semms good, thanks for the idea! So it's still a lot of clicks but at least don't have to worry about multiple tables, good stuff.
P_D_G ,
seem like you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebcThanks for the info, however, I'd like to achieve this in PQ if possible and create calculations based on the end result - great idea putting this into a measure, however, it doesn't help me out this time.
