Forum Discussion

GeoffHill's avatar
GeoffHill
Frequent Visitor
5 years ago
Solved

DAX to M language

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.

 

Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

     

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

    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}?)

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.