Understanding The “Evaluation resulted in a stack overflow” Error In Power Query In Excel And Power BI

If you’re writing your own M code in Power Query in Power BI or Excel you may run into the following error:

Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

If you’re a programmer you probably know what a stack overflow is; if you’re not you might search for the term, find this Wikipedia article and still have no clue what has happened. Either way it may still be difficult to understand why you’re running into it in Power Query. To explain let’s see some examples.

First, a really simple one. You can write recursive functions – functions that call themselves – in Power Query, although as you might suspect that is generally a bad idea because they are difficult to write, difficult to understand, slow and may result in the error above. Consider the following function called MyRecursiveFunction which references a parameter of type number called MaxDepth:

(counter as number) as number=>
if counter>MaxDepth then counter else @MyRecursiveFunction(counter+1)

The function takes a number and calls itself, passing in a value one greater than the number that was passed to it, until the number passed is greater than MaxDepth. So, if the value of MaxDepth is 3 and you call the function and pass it the value of 1, like so:

MyRecursiveFunction(1)

…then you’ll get the value 4 back:

So far so good. But how long can a function in M go on calling itself? As you can imagine, it’s not forever. So, when you hit the point where the function can’t go on calling itself then you get the error above. For example if you try setting MaxDepth to 100000 then you’ll get the stack overflow error above instead of 100001:

As a result it’s almost always a good idea to avoid recursion in Power Query and use functions like List.Transform, List.Generate or List.Accumulate to achieve the same result. A great example of this is shown in the Power Query custom connector documentation in the section on handling APIs that return results broken up into pages with the Table.GenerateByPage code sample.

You may still get this error even when you’re not explicitly using recursion though, as a result of lazy evaluation. Consider the following query which uses List.Accumulate to generate a table with a given number of rows:

let
  //define a table with one row and one column called x
  MyTable = #table(type table [x = number], {{1}}), 
  //specify how many rows we want in our output table
  NumberOfTimes = 3, 
  //Use List.Accumulate to create a table with this number of rows
  //By calling Table.Combine
  CombineAllTables = List.Accumulate(
    {1 .. NumberOfTimes}, 
    null, 
    (state, current) => if current = 1 then MyTable else Table.Combine({state, MyTable})
  )
in
  CombineAllTables

Here’s the output, a table with three rows:

But how does it get this result? With NumberOfTimes=3 you can think of this query lazily building up an M expression something like this:

Table.Combine({Table.Combine({MyTable, MyTable}), MyTable})

…which, once List.Accumulate has finished, suddenly all has to be evaluated and turned into a single table. Imagine how much nesting of Table.Combine there would be if NumberOfTimes was a much larger number though! And indeed, it turns out that you can’t make lots and lots of calls to Table.Combine without running into a stack overflow. So if NumberOfTimes=100000 like so:

let

  //define a table with one row and one column called x
  MyTable = #table(type table [x = number], {{1}}), 
  //specify how many rows we want in our output table
  NumberOfTimes = 100000, 
  //Use List.Accumulate to create a table with this number of rows
  //by calling Table.Combine
  CombineAllTables = List.Accumulate(
    {1 .. NumberOfTimes}, 
    null, 
    (state, current) => if current = 1 then MyTable else Table.Combine({state, MyTable})
  )
in
  CombineAllTables

…then, after a minute or so, you get the “Evaluation resulted in a stack overflow and cannot continue” error again.

Rewriting the query so you build up the list of tables first and only call Table.Combine once at the end avoids the problem and is much faster:

let
  //define a table with one row and one column called x
  MyTable = #table(type table [x = number], {{1}}), 
  //specify how many rows we want in our output table
  NumberOfTimes = 100000, 
  //create a table with NumberOfTimes rows
  CombineAllTables = Table.Combine(List.Repeat({MyTable}, NumberOfTimes))
in
  CombineAllTables

It’s also possible to solve the problem by forcing eager evaluation inside List.Accumulate but this is extremely tricky: there’s an example of this on Gil Raviv’s blog here.