Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
6 years ago
Solved

Convert DAX to M

Hi All,   I am trying to move all my calculated colums to the query editor which requires to translate DAX statement to M. I am not sure it is always possible. Here is an example where I can't make...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Arnault_ ,

    first I had to convert the values from the column TS05_packing_date into something that my Power Query was able to recognize as a date, this means my table looks like this:

    Here is my solution that is based on two steps:

    1. Determinig the min date per order_id

    For this I grouped the table by order_id, but also added the aggregation function "All Rows"

    After expanding the group my table looks like this:

    2. Adding a custom column to check the date

    I added a custom column "checkFirstDate" using this M expression:

    if [TS05_packing_date] <= Date.AddDays([MinDate], 😎 then "yes" else "no"

    Finally the table looks like this, of course the the column "MinDate" can be deleted from the table as an additional step:

     

    Hopefully this provides you with some ideas, how to tackle your requirement.

     

    Regards,

    Tom

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey Arnault_ ,

 

the answer to your question "is it better than your DAX solution" (slightly rephrased), is simply this: it depends 🙂

To take a decision, it's absolutely necessary to read this article: https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 

As there are just 2 unique value "yes" and "no", all the downsides of DAX based columns can be neglected.

 

On the other hand, here is some M code that does not use the expand columns, instead it uses the M function Table.Join inline, maybe you want to test it with your larger table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyi9KSS2Kz0xR0lEqSEzOzsxLh3BCgg1M42EiKYklqUCxtMyi4hKYoFKsTrSSv4GBIVAiAEIZGRha6hoY6oI5lanFaEqMkJUYYVVijKzEGKsSE7gSI11DSyAnLx9NhSlchbGuoSmKCiOICjN0FUjWQJWYIykxghkSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"order_id", type text}, {"packing_id", type text}, {"TS05_packing_date", type date}, {"first_packing", type text}}),


    #"Grouped Rows" = Table.Group(#"Changed Type1", {"order_id"}, {{"MinDate", each List.Min([TS05_packing_date]), type date}}),
    TableJoin = Table.Join(#"Changed Type1" , "order_id" , #"Grouped Rows" , "order_id"  ),
    
    #"Added Custom" = Table.AddColumn(TableJoin, "checkFirstDate", each if [TS05_packing_date] <= Date.AddDays([MinDate], 😎 then "yes" else "no"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"TS05_packing_date", Int64.Type}})

in
    #"Changed Type2"

 

Regards,

Tom

Arnault_'s avatar
Arnault_
Resolver III
6 years ago

Hi TomMartens ?

I really appreciate the time you dedicate to mly request and thank you for the proposed solution.

Cheers