Remove From My Forums

"Stack overflow" error with List.Accumulate

Question
0

Sign in to vote

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

Monday, November 4, 2019 4:52 PM

Reply

|

Quote

All replies

0

Sign in to vote

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.

Tuesday, November 5, 2019 5:10 PM

Reply

|

Quote
0

Sign in to vote

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!

Tuesday, November 5, 2019 6:53 PM

Reply

|

Quote

Moderator

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

Wednesday, November 6, 2019 3:09 PM

0

Sign in to vote

I believe it's the Table.Combine in the return value of the function that's the problem. You should probably buffer the result of Table.Combine just as you do in the main part of the query (a place where it's probably not necessary).

Thursday, November 7, 2019 3:13 PM

Reply

|

Quote

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]

Monday, November 11, 2019 2:28 PM

0

Sign in to vote

Hi Cory. Were you able to make any progress on this?

Ehren

Tuesday, December 10, 2019 10:16 PM

Reply

|

Quote

Owner

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Asked by:

"Stack overflow" error with List.Accumulate

Question

All replies