Rewriting formula DAX in the language M

[BI2B]

Hi
Need help rewriting formula dax in the language m - add a custom column
my formula: Previous = LOOKUPVALUE('445'[datetime],'445'[Rank],'445'[Rank]-1)
goal: append two queries (with the participation of the column)
similar theme unanswered: 
http://community.powerbi.com/t5/Desktop/How-to-append-queries-to-calculated-columns/m-p/40520#U40520

It is also open to other solutions
Thank you

[v-qiuyu-msft · Accepted solution]

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

[BI2B]

Hi Qiuyun,

Thank you very much for your help and solution