Change DAX measure into Power Query Editor Custom Column

[NickProp28]

Dear Comunnity,

I have a DAX measure as below, and would like to change it into power query custom column. I wonder isit able to do that, because if I create a measure with DAX, I unable to use the merge queries feature in Power Query Editor. 
In this case, without using the merge queries, I have to use Crossjoin in DAX which will consume a lots of time when I refresh the report.

DAX need change to power query

Unique Match Column = 
var consignee=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignor=CALCULATE(DISTINCTCOUNTNOBLANK(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber])
var consignor1=CALCULATE(Max(Client[Consignor]),ALLEXCEPT(Client,Client[ConsolNumber]))
var match=SWITCH(TRUE(),
AND(consignor=1,consignee=1),consignee1,
consignee=1,consignee1,
consignor=1,consignor1,
AND(ISBLANK(consignor),consignee<>1),"BLANK",
AND(ISBLANK(consignee),consignor<>1),"BLANK",
"MIX")
return match

Hope you guys can give me some advice.

Attached with the pbix: https://ufile.io/5l2rjw9a

Greatly appreciate any help and thanks for your attention.

[lbendlin]

var consignee1=CALCULATE(Max(Client[Consignee]),ALLEXCEPT(Client,Client[ConsolNumber])

This would sort Consignee (and Consignor) alphabetically. Is that what you are intending? 

[NickProp28]

Dear @lbendlin ,

Thank for the reply. Yes correct. 

Here's the result when consignee or consignor condition have been meet.

If there is more than one brands in Consignee/Consignor column, there will have result as 'MIX'.

So doesnt matter if is sort in aplhabetically. 

Thank you

[Ashish_Mathur]

Hi,

Please explain the logic.  For C001, why is the answer NIKE and not ADIDAS?  Please also explain the logic of the other ConsolNumbers.

[NickProp28]

Dear @Ashish_Mathur ,

Thank for your attention.

Some operations have incorrectly input the data into system. Supposedly in one consol number, there is only have one brand in Consignee/Consignor. Like C002 and C003, this is correct input.

After we have the discussion, we come out some filter as below:
i.) if only one brand in consignee or consignor, prioritise Consignee. (C001)

ii.) if only one brand in consignee, and more than one brand in consignor, prioritise Consignee. (C002)
iii.) if only one brand in consignor, and more than one brand in consignee, prioritise Consignor. (C003)

iv) both of it having more than one brand, stated "MIX" (c004)

v.) if there have null value in consignee, but consignor have one brand, prioritise Consignor (Same if consignor have null value) 

vi.) if consignee/consignor have null value but also  one brand, like C009 and C010. We will prioritise this particular column.  

So at the end, we have captured Consignee/Consignor (which is correct input) and take action on those MIX/Blank consol number. 

I checked on this calculate column, all result had return correctly. But I would like to have some help to convert this into Power Query. 

Hope you can provide some help on it. Greatly appreciate!

[Ashish_Mathur]

Hi,

It should be easier to write this as a calculated column formula using DAX.

[NickProp28]

@Ashish_Mathur,

I want this calculated column convert into Power Query. And I need some help on it.  

[Ashish_Mathur]

I will not be able to help with a Power Query solution.

[NickProp28]

Hi @Ashish_Mathur,

Thanks for your attention. 

[lbendlin]

DAX measures cannot be rewritten in Power Query because they are computed dynamically based on user filter selections.

Your item above seems to be a calculated column (not a measure) so it can be converted to Power Query.  However your sample PBIX (thank you for posting it) points to a local file data source

'C:\Users\nikchoo\Desktop\Testingconsignornee.xlsx'

that we of course don't have access to. Please post the sample data as well.

[NickProp28]

Dear @lbendlin,

Thank you so much for your attention.

My apologies. Here's the pbix and excel file: https://ufile.io/q2pty4w9

Greatly appreciated.

[NickProp28]

Dear @lbendlin ,

Good day!
At your convenience would you mind have an update on this please? 
Much appreciated.