Transpose data from calculated columns

[tgordon]

I have a set of data organized as shown below:

Project	Quarter for Revenue	Y1 Revenue	Y2 Revenue	2020 Q1	2020 Q2	2020 Q3	2020 Q4	2021 Q1	2021 Q2	2021 Q3	2021 Q4
Project A	2020 Q3	50	75	0	0	50	50	50	50	75	75
Project B	2020 Q1	25	60	25	25	25	25	60	60	60	60
Project C	2021 Q2	10	30	0	0	0	0	0	10	10	10
Project D	2021 Q3	100	200	0	0	0	0	0	0	100	100

The "YYYY Q#" columns are calculated from the other columns.  I would like to create a new table that looks like below:

Quarter	Project A	Project B	Project C	Project D
2020 Q1	0	25	0	0
2020 Q2	0	25	0	0
2020 Q3	50	25	0	0
2020 Q4	50	25	0	0
2021 Q1	50	60	0	0
2021 Q2	50	60	10	0
2021 Q3	75	60	10	100
2021 Q4	75	60	10	100

Is there a way to do this in power bi?  I found that you can do it with power query, but that doesn't work on calculated columns. I want to plot quarter vs revenue by project and this is the only way I found to make it work.

Thanks for your help!

[Anonymous]

@tgordon 

It is recommended to create a custom column, and then shape the data using some unpiviot, remove and transpose in Power Query.

Best,
Paul

[az38 · Accepted solution]

Hi @tgordon 

you could create a custom column in Power Query. it could be the same as your calculated column (but it depends on your data model). after that - pivot/unpivot functions in power query

do not hesitate to give a kudo to useful posts and mark solutions as solution

[parry2k]

@tgordon it will be easy to provide a solution if you share how does your raw data looks like and there is potential solution looking at it.