Forum Discussion
Transform / Unpivot Columns
Hello I have below 4 columns with data but need to unpivot somehow to get desired results. I 've tried all the possible unpivot actions but I can't get it work. Any thoughts?
Current:
| Store | Timeframe | Average Flu Volume | Avg Rx Volume |
| 123 | Winter 2023 | 0.896696428 | 192 |
| 45 | Winter 2023 | 1.805026641 | 183 |
| 80 | Winter 2023 | 1.368312214 | 168 |
| 20 | Winter 2023 | 0.483249572 | 166 |
| 50 | Winter 2023 | 1.010349568 | 130 |
| 60 | Summer 2024 | 0.756196089 | 117.75 |
| 70 | Summer 2024 | 1.674370276 | 312.75 |
| 80 | Summer 2024 | 1.257343793 | 182 |
Desired:
| Store | Winter 2023 - Average Flu Volume | Winter 2023 - Avg Rx Volume | Summer 2024 - Average Flu Volume | Summer 2024 - Avg Rx Volume |
| 123 | 0.896696428 | 192 | ||
| 45 | 1.805026641 | 183 | ||
| 80 | 1.368312214 | 168 | ||
| 20 | 0.483249572 | 166 | ||
| 50 | 1.010349568 | 130 | ||
| 60 | 0.756196089 | 117.75 | ||
| 70 | 1.674370276 | 312.75 | ||
| 80 | 1.257343793 | 182 |
Hi jcastr02
If it is for visualization, you just need to use a Hierarchical matrix :Note that I added conditional column from PQ, for sorting in the needed order these columns
Pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi jcastr02 It is not an efficient way to work, but yes it is possible
It is a lot of steps :
You need to duplicate the tables twice, to pivot one table for average Rx
Second for Avg Rx and to merge these pivoted tables.
The updated pbix is attached, you can follow my steps.
**I didn't format column names, you can modify them according to your needs.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Hi jcastr02
If it is for visualization, you just need to use a Hierarchical matrix :Note that I added conditional column from PQ, for sorting in the needed order these columns
Pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thanks Ritaf1983 I am needing to add it to a table visual that has other items tied to it - would there be a way to transform it on back end - so I can just pull the below in as columns to the visual?
Winter 2023 - Average Flu Volume Winter 2023 - Avg Rx Volume Summer 2024 - Average Flu Volume Summer 2024 - Avg Rx Volume Hi jcastr02 It is not an efficient way to work, but yes it is possible
It is a lot of steps :
You need to duplicate the tables twice, to pivot one table for average Rx
Second for Avg Rx and to merge these pivoted tables.
The updated pbix is attached, you can follow my steps.
**I didn't format column names, you can modify them according to your needs.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
