Transform / Unpivot Columns

[jcastr02]

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

[Ritaf1983]

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

[jcastr02]

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

[Ritaf1983 · Accepted solution]

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

[jcastr02]

That worked, Thank you 🙂