Forum Discussion
Conditional columns
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 🙂 .
8 Replies
- amitchandak
Super User
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())
- Jihwan_Kim
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.
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
Hi, Anonymous
Thank you for your feedback.
In your sample case, I see that your desired outcome also showed two rows for Jan.
