Forum Discussion
How to avoid circular dependency using previous row
Below is a sample data - first 3 columns are my inputs. Total Supply, Match and Long are my calculated columns. Total Supply = Current Quarter Supply + Previous quarter Long. Example Total Suppl...
Hi,
That is definitely cicular logic. I misread your question initially.
Well, it may be circular but List.Accumulate can handle it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGEUamSrE60UpGIL4FkDAxBPONQUyQoJERmG8C4ptC+bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, Supply = _t, Outlook = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", Int64.Type}, {"Supply", Int64.Type}, {"Outlook", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Accumulate({1..[Quarter]},[Total=0, Match=0, Long=0],(state,current)=>[
Total = #"Changed Type"{current-1}[Supply] + state[Long],
Match = List.Min({Total,#"Changed Type"{current-1}[Outlook]}),
Long = Total-Match ]
)),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Total", "Match", "Long"}, {"Total", "Match", "Long"})
in
#"Expanded 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".
Hi,
Thank you for sharing this.
Ashish_Mathur I learned how to better carry multiple variables through the iterations, and how (and when) to address them in in each cycle ( like the " state[Long] " example). I am more and more impressed by the versatility of List.Accumulate.