Asked by:
"Stack overflow" error with List.Accumulate

Question
-
Hi all,
I am using List.Accumulate to allocate stock to various sales orders. I found the post below very helpful as I was trying to accomplish something very similar (I posted this question as a comment, but thought it might be better to ask a new question).
https://social.technet.microsoft.com/Forums/en-US/292335df-67fc-4e1b-b7fb-249cade995bc/recursive-use-of-a-column?forum=powerquery
However, while this approach worked on a smaller sample dataset (~100 to 1,000 rows) when I was testing it, I'm now having problems with my actual data (which is ~5,000 to 10,000 rows). I get a "Expression.Error: Evaluation resulted in a stack overflow and cannot continue" when I try to run it on the larger dataset. Is this a limitation of List.Accumulate? Any ideas or tips to try to diagnose the issue?
I'm happy to share my code, but thought I would see if it's a known issue before diving in...
Thanks!
Cory
All replies
-
Without seeing the code, it's hard to say exactly why this is happening or how to avoid it. But broadly speaking, M is a lazy language and some of the things you compute actually result in promises instead of immediate values. If the combination function passed to List.Accumulate results in a promise, then the result of accumulating over your list is a promise on top of a promise on top of... etc. At some point, when the actual value is calculated, it's done recursively -- the chain of promises is too long and the stack flows over.
-
Hi Cory,
it would be a "known issue" if you'd skip the "Partitioned"-step. See for more details here: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/
So make sure you're applying the function on item-level.
Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks! -
Hi all,
Thanks for the replies. Below is my code, maybe that will help narrow down the issue. For context, it is taking one large table that contains Sales Order (SO), Purchase Order (PO), and inventory level information and transforms the data into SOTable (all of the sales order information) and SupplyTable (combines on hand inventory with incoming POs). The purpose of the query is to allocate the supply to the appropriate sales order based on a first in first out rule.
Imke, yes I did use the "Partitioned" step (named SOTableGrouped in my code).
Thanks,
Cory
EDIT: I have a filter (FilterRows) for testing purposes that is set to only use the first 500 rows of the data source. It works fine with 500, but when I increase it to say 3,000 rows I get the stack overflow error.
Sample of SOTable:
SALES_ORDER
LINE_ID
ITEM_NO
BRANCH_PLANT
ITEM_QTY
7031
1000
CAFSLC-3
351
5
1032173
1000
P843763-117-048-915
260
1
1056905
1000
MKR651800L5
301
2
1059876
1000
63437-07-JB
351
3
1083023
1000
63437-07-JB
351
8
1100910
1000
SE1A210C2AM-CSA
301
1
1104382
1000
SERPUMPQC
260
1
Sample of SupplyTable:
ITEM_NO
BRANCH_PLANT
RemainingSupply
PO_NO
PO_LINE_ID
PO_PROMISED_DATE
25271207
301
1
Stock
null
null
6902
301
1
Stock
null
null
CAFSLC-3
351
544
Stock
null
null
CAFSLM-3
351
9365
Stock
null
null
3032407
351
3
Stock
null
null
DAB72
260
30
Stock
null
null
BG1014
260
140
Stock
null
null
96M360Q-TFE
301
2
Stock
null
null
AP843768-10079029
260
48
216699
3000
10/31/2019 12:00:00 AM
AP843732-10079027
260
334
216699
1000
10/31/2019 12:00:00 AM
AP843732-10079027
260
380
220784
1000
11/1/2019 12:00:00 AM
AP843768-10079029
260
58
220784
7000
11/1/2019 12:00:00 AM
TOH2-40-250-FM-060R1
621
2
225060
1000
11/1/2019 12:00:00 AM
M code:
let fnAllocatePOs = (Previous, Current) => let MatchingItemBranch = Table.Buffer(Table.SelectRows(Previous[RemSupply], each ([ITEM_NO] = List.First(Current[ITEM_NO])) and ([BRANCH_PLANT] = List.First(Current[BRANCH_PLANT])))), OtherItems = Table.Buffer(Table.SelectRows(Previous[RemSupply], each not(([ITEM_NO] = List.First(Current[ITEM_NO])) and ([BRANCH_PLANT] = List.First(Current[BRANCH_PLANT]))))), SupplyAndDemand = Table.Combine({Table.AddColumn(Current, "RemainingSupply", each -[ITEM_QTY]), MatchingItemBranch}), SOFillDown = Table.FillDown(SupplyAndDemand,{"SALES_ORDER", "ITEM_QTY", "LINE_ID"}), TableType = Value.Type(Table.AddColumn(SOFillDown, "RunningTotal", each null, type number)), RunTot = List.Skip(List.Accumulate(SOFillDown[RemainingSupply], {0}, (Previous2, Current2) => Previous2 & {List.Last(Previous2) + Current2})), AddRunTot = Table.FromColumns(Table.ToColumns(SOFillDown) & {RunTot}, TableType), RemoveAllocated = Table.SelectRows(AddRunTot, each [RunningTotal] >= 0), // Add PO to SO line AddToAllocation = Table.RemoveColumns(Table.FirstN(RemoveAllocated,1), {"RemainingSupply", "RunningTotal"}), // Update remaining supply table UpdatedSupply = Table.RemoveColumns( Table.ReplaceValue(RemoveAllocated, each [RemainingSupply], each if [RemainingSupply] > [RunningTotal] then [RunningTotal] else [RemainingSupply], Replacer.ReplaceValue, {"RemainingSupply"}), {"RunningTotal", "SALES_ORDER", "ITEM_QTY", "LINE_ID"}), Result = [Allocation = Table.Combine({Previous[Allocation], AddToAllocation}), RemSupply = Table.Combine({UpdatedSupply, OtherItems})] in Result, Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="PHData"]}[Content]), FilterRows = Table.FirstN(Source, 500), FilterOn = 1, FilterToggle = if FilterOn = 1 then FilterRows else Source, Types = Table.TransformColumnTypes(FilterToggle,{{"SHIP_TO", Int64.Type}, {"SHIP_TO_NAME", type text}, {"GEOGRAPHIC_REGION", type text}, {"SALES_ORDER", Int64.Type}, {"ORDER_TYPE", type text}, {"LINE_TYPE", type text}, {"PO_NO", Int64.Type}, {"SUPPLIER_NUMBER", Int64.Type}, {"SUPPLIER_NAME", type text}, {"PO_LAST_STATUS", Int64.Type}, {"PO_NEXT_STATUS", Int64.Type}, {"SHORT_ITEM", Int64.Type}, {"ITEM_NO", type text}, {"BRANCH_PLANT", Int64.Type}, {"DESCRIPTION", type text}, {"QOH", Int64.Type}, {"SALES_PROMISE_DATE", type datetime}, {"SALES_REQUEST_DATE", type date}, {"ORIGINAL_PROMISE_DATE", type datetime}, {"SALES_ORDER_DATE", type datetime}, {"SALES_SHIP_DATE", type datetime}, {"PO_PROMISED_DATE", type datetime}, {"ITEM_QTY", Int64.Type}, {"QTY_SHIPPED", Int64.Type}, {"QTY_BACKORDERED", Int64.Type}, {"EXTENDED_UNIT_PRICE", type number}, {"SRP3", type text}, {"SRP5", type text}, {"PRP2", type text}, {"SRP4", type text}, {"PRP6", type text}, {"LEAD_TIME", Int64.Type}, {"TO_ID", type text}, {"TRANSACTION_ORIGINATOR", type text}, {"HOLD_CODE", type text}, {"LAST_STATUS", Int64.Type}, {"NEXT_STATUS", Int64.Type}, {"ABAC15", type any}, {"FREIGHT_HANDLING_CODE", type text}, {"STOCKING_TYPE", type text}}), SOTable = Table.Buffer( Table.Sort( Table.RemoveColumns(Table.Group(Types, {"SALES_ORDER", "LINE_ID", "ITEM_NO", "BRANCH_PLANT", "ITEM_QTY"}, {{"All", each _, type table}}), {"All"}), {{"LINE_ID", Order.Ascending}, {"SALES_ORDER", Order.Ascending}})), POTable = Table.RenameColumns( Table.Sort( Table.SelectRows( Table.RemoveColumns(Table.Group(Types, {"PO_NO", "PO_LINE_ID", "SUPPLIER_NUMBER", "SUPPLIER_NAME", "PO_LAST_STATUS", "PO_NEXT_STATUS", "ITEM_NO", "BRANCH_PLANT", "PO_PROMISED_DATE", "PO_QTY"}, {{"All", each _, type table}}), {"All"}), each ([PO_NO] <> null)), {{"PO_NO", Order.Ascending}, {"PO_PROMISED_DATE", Order.Ascending}}), {{"PO_QTY", "RemainingSupply"}}), ItemStock = Table.AddColumn( Table.RenameColumns( Table.SelectRows( Table.RemoveColumns(Table.Group(Types, {"ITEM_NO", "BRANCH_PLANT", "QOH"}, {{"All", each _, type table}}), {"All"}), each ([QOH] <> 0)), {{"QOH", "RemainingSupply"}}), "PO_NO", each "Stock"), SupplyTable = Table.Buffer(Table.Combine({ItemStock, Table.RemoveColumns(POTable,{"SUPPLIER_NUMBER", "SUPPLIER_NAME", "PO_LAST_STATUS", "PO_NEXT_STATUS"})})), SOTableGrouped = Table.Group(SOTable, {"SALES_ORDER", "LINE_ID", "ITEM_NO", "BRANCH_PLANT", "ITEM_QTY"}, {{"All", each _, type table}}), AllocatePOs = List.Accumulate(SOTableGrouped[All], [Allocation = #table({}, {{}}), RemSupply = SupplyTable], (Previous, Current) => fnAllocatePOs (Previous, Current)), DisplayRemSupply = AllocatePOs[RemSupply], DisplayAllocation = AllocatePOs[Allocation] in DisplayAllocation
- Edited by Cory Minkhorst Wednesday, November 6, 2019 3:12 PM clarification
-
-
Hi Curt,
Thanks for the help. Unfortunately that doesn't seem to have worked, still getting the stack overflow error. It actually seems to happen with a fewer number of records now. Before the query would run OK with ~2,000 rows, but now I get the error with that number.
I updated the last few lines of the function as per below. Did I understand your suggestion properly? I also tried moving/adding/removing some of the other buffers in the function and main query to see if it would help, but no luck.
Is the idea that using Table.Buffer is forcing the "promises" to evaluate solving the chain of promises problem you mentioned in your previous reply?
Thanks,
Cory
// Add PO to SO line AddToAllocation = Table.RemoveColumns(Table.FirstN(RemoveAllocated,1), {"RemainingSupply", "RunningTotal"}), AddPrevAlloc = Table.Buffer(Table.Combine({Previous[Allocation], AddToAllocation})), // Update remaining supply table UpdatedSupply = Table.RemoveColumns( Table.ReplaceValue(RemoveAllocated, each [RemainingSupply], each if [RemainingSupply] > [RunningTotal] then [RunningTotal] else [RemainingSupply], Replacer.ReplaceValue, {"RemainingSupply"}), {"RunningTotal", "SALES_ORDER", "ITEM_QTY", "LINE_ID"}), AddPrevSupply = Table.Buffer(Table.Combine({UpdatedSupply, OtherItems})), Result = [Allocation = AddPrevAlloc, RemSupply = AddPrevSupply]
-