DAX to Power Query - Offset Functions

[needshelp20]

Does anybody know how to convert the following code from DAX to Power Query? I'm not sure what the equivalent function for Calculate and Offset is in M Query. There's an example of what the table should look like at the bottom of the image.

[lbendlin · Accepted solution]

Does it need to work with the year column or is it acceptable to add an index column?  Remember that in Power Query you can address any row  via {x}  where x can be a row number or a condition.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTI2NQUyTPTMzZRidUCiRgZAUXMzSwtjUz0LA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Sales Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Sales Amount", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Prev Year Sales", (k)=> try #"Changed Type"{[Year = k[Year]-1]}[Sales Amount] otherwise null,Currency.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.