DAX to M language

[GeoffHill]

Hi,

I'm trying to convert the below calculated column in DAX to Power Query M language so the Calculated column can refresh with the scheduled refresh.

DAX for Calculated column is:

WS Name = CALCULATE(FIRSTNONBLANK('(Discount) Supplier Outlet'[Primary Wholesaler Name],1),FILTER(ALL( '(Discount) Supplier Outlet'),'(Discount) Supplier Outlet'[Outlet__c]='(Discount) Outlet Contract WS Number'[Outlet__c]))

Looking for the equivalent M language to generate an additioncal column in the table.

[v-yingjl · Accepted solution]

Hi @GeoffHill ,

Since the equiment dax formula in this case is min(), you can try this query:

MIN ( <column> ) = FIRSTNONBLANK ( <column>, 1 )

let
    Source = Table.NestedJoin(#"(Discount) Outlet Contract WS Number", {"Outlet__c"}, #"(Discount) Supplier Outlet", {"Outlet__c"}, "(Discount) Supplier Outlet", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Result", each List.Min([#"(Discount) Supplier Outlet"][Primary Wholesaler Name]),type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"(Discount) Supplier Outlet"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

Table.AddColumn(#"(Discount) Outlet Contract WS Number","WS Name",each List.Skip(Table.SelectRows(#"(Discount) Supplier Outlet",(x)=>x[Outlet__c]=[Outlet__c])[Primary Wholesaler Name],each _=null or _=""){0}?)