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.
Hi,
I have a table with monthly company data for 3 years. I am trying to reshape my table and make new columns for some of the metrics using conditional columns. My table looks like below:
Company | Month | Year | Metric name | Metric value |
A | Jan | 2020 | Revenue | 100 |
A | Jan | 2020 | Cost | 50 |
A | Feb | 2020 | Revenue | 110 |
A | Feb | 2020 | Cost | 70 |
A | Feb | 2020 | Tax | 10 |
B | Jan | 2020 | Revenue | 500 |
B | Jan | 2020 | Cost | 200 |
B | Jan | 2020 | Tax | 20 |
When I make the conditional columns, I still have seperate rows for each month and each metric and it looks like this:
Company | Month | Year | Revenue | Cost |
A | Jan | 2020 | 100 | |
A | Jan | 2020 | 50 | |
A | Feb | 2020 | 110 | |
A | Feb | 2020 | 70 | |
B | Jan | 2020 | 500 | |
B | Jan | 2020 | 200 |
But I want it to looks like this:
Company | Month | Year | Revenue | Cost |
A | Jan | 2020 | 100 | 50 |
A | Feb | 2020 | 110 | 70 |
B | Jan | 2020 | 500 | 200 |
Any suggestion is appreciated 🙂 .
Hi, @Anonymous
Inside the Power Query Editor,
1. filter out the Tax rows
2. select Metric Name and Materic value columns
3. pivot those
4. Selecte Value column as Metric Value
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
@Jihwan_Kim Unfortunately the problem with date still exists. I did not get one row for january with Revenue and Cost values in the same row...
@Jihwan_Kim I see the confusion. That is for another company (company B). So I'd like to have one row for each month per company 🙂 .
Hi,
Thank you for your feedback.
Please correct me if the link down below is not what you are looking for.
You can open it and go into Power Query, and you can check whether it is correctly done or not.
Thank you very much.
https://www.dropbox.com/s/9h1cobfb3i4te43/Fereshteh.pbix?dl=0
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Thank you for your help, unfortunately I could not make it work with your proposed solution. I removed my Revenue and Cost columns and kept the year, company and the Year-to date column that I calculated from Revenue. It looks fine now.
Hi, @Anonymous
Thank you for your feedback.
In your sample case, I see that your desired outcome also showed two rows for Jan.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Anonymous , first table when you create columns, Second table is when you create a measure.
Even if you create column, when you show on visual, it will group like second table
new columns
revenue= if([Metric name] ="Revenue",[Metric Value], blank())
Cost= if([Metric name] ="Cost",[Metric Value], blank())
Hi,
Sorry, I am new to Power BI, do you mean that I should make a "custom column"? In that case, I get an error related to the [metric value].
Thanks.
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |