Forum Discussion
Solved
Transpose data from calculated columns
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!
3 Replies
It is recommended to create a custom column, and then shape the data using some unpiviot, remove and transpose in Power Query.
Best,
Paul
