Forum Discussion

konradjonsson's avatar
konradjonsson
Helper II
6 years ago
Solved

Translate DAX to Power Query M language

Hi.

 

I need help to translate the following DAX formula into a M formula in Power Query.

 

1MONTH WTY = if(DATEDIFF('Table'[Invoice Date];max(Table'[Invoice Date]);day)>31;'Table'[1mth];BLANK())

 

I have tried the following, but it gives an error as result (no issue with the syntax, it is the output that is erroneous):

#"Added Custom" = Table.AddColumn(#"Changed Type4", "1MONTH WTY", each if Duration.Days(Duration.From(Table.Max[Invoice Date] - [Invoice Date]))>31 then [#"1mth"] else "")

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi konradjonsson 

     

    Try something like this

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            durat = Duration.Days( List.Max( #"Changed Type"[Invoice Date] /* this is the previous step */) - [Invoice Date] ) > 31, 
            output = if durat then  [#"1mth"] else "" 
        in 
            output
    )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

  • dax's avatar
    dax
    6 years ago

    Hi konradjonsson, 

    You could refer to below M code to see whether it work or not

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvLCQAgEAPRXvYsJFlQtBax/zZUPPi7PmZqNacK5EgWzFrYEF8Qh+ggweNPs+JB/Efi+TJ0X3lNQ1oH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, M1mth = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"M1mth", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Duration.Days(Duration.From(List.Max(#"Changed Type"[date])-[date]))>31 then [M1mth] else null)
in
    #"Added Custom"

     

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • konradjonsson's avatar
      konradjonsson
      Helper II
      6 years ago

      Hi.

      I am not so savy on uploading table data. I attach a snapshot, where I have highlighted the issue in red.

       

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi konradjonsson 

     

    Try something like this

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            durat = Duration.Days( List.Max( #"Changed Type"[Invoice Date] /* this is the previous step */) - [Invoice Date] ) > 31, 
            output = if durat then  [#"1mth"] else "" 
        in 
            output
    )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi konradjonsson, 

    You could refer to below M code to see whether it work or not

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvLCQAgEAPRXvYsJFlQtBax/zZUPPi7PmZqNacK5EgWzFrYEF8Qh+ggweNPs+JB/Efi+TJ0X3lNQ1oH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, M1mth = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"M1mth", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Duration.Days(Duration.From(List.Max(#"Changed Type"[date])-[date]))>31 then [M1mth] else null)
in
    #"Added Custom"

     

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.