Value in the previous row

[naveen73]

Hi All,

I am trying to achieve something with Power Query. I think it is best to describe my problem with a screen shot.

I want to calculate interest using the end principal of the previous month (cell J8). See screenshot 1.

The end principal is calculated by taking the previous principal and subtracting the interest and payment. See screenshot 2.

thanks,

Naveen

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACSliCWUqxOtJIRkAVEJnqmcCFjmJARXMgEKgQXMIUIGOuZQ4RiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Begin Principal" = _t, Interest = _t, Payment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Begin Principal", Currency.Type}, {"Interest", Currency.Type}, {"Payment", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End Principal", each List.Accumulate({1..[Month]},#"Changed Type"[Begin Principal]{0},(state,current)=>state-#"Changed Type"[Interest]{current-1}-#"Changed Type"[Payment]{current-1}))
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".

[naveen73]

Thanks so much for this. I think it is doing the trick!