Forum Discussion
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 (...
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".
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".
Thanks so much for this. I think it is doing the trick!
