Conditional columns

[Anonymous]

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 🙂 .

[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.

[Anonymous]

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...

[Anonymous]

@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 🙂 .

[Jihwan_Kim]

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 

[Anonymous]

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.

[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.

[amitchandak]

@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())

[Anonymous]

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.