Forum Discussion

naveen73's avatar
naveen73
Helper III
3 years ago
Solved

Value in the previous row

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

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    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".

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    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's avatar
      naveen73
      Helper III
      3 years ago

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