Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
It is recommended to create a custom column, and then shape the data using some unpiviot, remove and transpose in Power Query.
Best,
Paul
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |