Translate DAX to Power Query M language

[konradjonsson]

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 "")

[Mariusz · Accepted solution]

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 · Accepted solution]

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.

[Mariusz · Accepted solution]

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.

[Mariusz]

Hi @konradjonsson 

Can you create a small data sample?

Thanks

Mariusz

[konradjonsson]

Hi.

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