none
"Stack overflow" error with List.Accumulate RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    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
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Cory,

    trying to follow your solution, but missing the table from the Source-step:

     Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="PHData"]}[Content]),

    At first glance, your code shows a lot of areas for (speed) improvement, but need your data to actually understand what's going on.

    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!

    Sunday, December 15, 2019 7:41 AM
    |
    Moderator

All replies

  • Question
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    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
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

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

    Ehren

    Tuesday, December 10, 2019 10:16 PM
    |
    Owner
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Cory,

    trying to follow your solution, but missing the table from the Source-step:

     Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="PHData"]}[Content]),

    At first glance, your code shows a lot of areas for (speed) improvement, but need your data to actually understand what's going on.

    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!

    Sunday, December 15, 2019 7:41 AM
    |
    Moderator