Forum Discussion
Satya2804
3 years agoFrequent Visitor
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...
Satya2804
3 years agoFrequent Visitor
Hi Ashish
Yeah I can add a quarter end date as below:
| Quarter end date | Quarter | Supply | Outlook |
| 31-03-2023 | Q1 | 31 | 25 |
| 30-06-2023 | Q2 | 38 | 41 |
| 30-09-2023 | Q3 | 42 | 22 |
| 31-12-2023 | Q4 | 45 | 22 |
Ashish_Mathur
3 years agoSuper User
Hi,
That is definitely cicular logic. I misread your question initially.
- lbendlin3 years agoSuper User
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".
- Ashish_Mathur3 years agoSuper User
Hi,
Thank you for sharing this.
- lbendlin3 years agoSuper User
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.