Rewriting formula DAX in the language M

Hi BI2B,

 

In your scenario, you can use UI to generate the backend M query.

 

1. Add a index column from 0.

2. Add a custom column like below:

=AddInd[datetime]{[Index]-1}

3. Replace the error with null value.

4. The backend M query like below:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1NNI1VNJRAmMDpVgdhLARUAiMUYWNgUJgjCpsAhQCY6BwLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datetime = _t, Rank = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"datetime", type date}, {"Rank", Int64.Type}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}),
    AddInd = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(AddInd, "Custom", each AddInd[datetime]{[Index]-1}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}})
in
    #"Replaced Errors"

 

Reference:

Absolute and Relative References in Power Query: R1C1 Excel-style approach

How can I do calculation based on next/prior row in Power Query?

 

Best Regards,
Qiuyun Yu