Compare two rows based on criteria in power query and power bi

[Suyheng]

Dear All,

I need your help for geting the result column as below:

I want to have a power query and a dax code to get the result column based on Criteria Column.

if the criteria column contain "cash" then the result column return cash for the same TranCode Column and the rest

that don't meet the Criteria Column return null.

Please noted that for large data set I can merge column to get the result in power query but the pefromance is too slow and i want a better way for power query code that avoid merging column.

TranCode	Acc Decription	Criteria	Result
A1	Exp	null	Cash
A1	Cash	Cash	Cash
B1	Exp	null	null
B1	Payable	null	null
C1	Exp	null	null
C1	Payable	null	null
D1	Exp	Cash	Cash
D1	Cash	null	Cash

Appriciate for you kind help.

Best regards,

Heng

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(CALCULATE(CALCULATE(COUNTROWS(Data),Data[Criteria]="Cash"),FILTER(Data,Data[TranCode]=EARLIER(Data[TranCode])))=1,"Cash",BLANK())

[Suyheng]

Dear Ashish_Mathur,

Very nice formula.

Do you have any solution in power query?

Thanks for your help.

Best regards,

Heng

[Ashish_Mathur]

Hi,

Thank you.  No i do not know how to solve this in the Query Editor.