Search code examples
excelrecursionclosurespowerquerym

Power Query Closures and Recursion; Textbooks on this?

Can closures in the Power Query M language be recursive?

Functions can be recursive. I name a function-query Fibonacci, defined as

(X) => 
let
    Fib = if X <= 1 then 1 else X + Fibonacci(X - 1)
in
    Fib

It works fine. But when I try and stand it up within a single query, it loses:

let
    Fib = (X)=> if X <= 1 then 1 else X + Fib(X - 1),
    Source = Table.FromRecords({[X=1],[X=2],[X=3],[X=5]}),
    UseIntFib = Table.AddColumn(Source, "Fib", each Fib([X]) )
in
    UseIntFib

The resulting table is

1    1
2    Error
3    Error
5    Error

with the errors' message being 'Fib not found, did you forget to import a module?'

Is there any other way around this? Or, if I need a recursive function, does it have to be stood up as its own query?

Meta queries -- In M, is a locally defined function like

Fib = (X)=> if X <= 1 then 1 else X + Fib(X - 1)

called a closure, or a lambda expression, or what? I've seen 'em used, just not heard 'em named. So I'm unsure how to search for posts about them.

Is there a good textbook or online source that would teach me all about Power Query closures? My go-to text, M is for (DATA) Monkey, says almost nothing about them. Mostly, I'd just like a whole bunch of examples. I want to know about

  • When to use them
  • Recursion and mutual recursion
  • How they relate / compare to using each
  • Multi-statement closures - using let inside a let .
  • Debugging them
Solution

  • If you want to call a function recursively, you need to add a @ as so called "scoping operator", so this code is OK (I just added @ to "Fib" in your code):

    let
    Fib = (X)=> if X <= 1 then 1 else X + @Fib(X - 1),
    Source = Table.FromRecords({[X=1],[X=2],[X=3],[X=5]}),
    UseIntFib = Table.AddColumn(Source, "Fib", each Fib([X]) )
in
    UseIntFib

    You can find more information in the Power Query Language Specification (October 2016): https://msdn.microsoft.com/en-us/library/mt807488.aspx?f=255&MSPPError=-2147217396

    (3.3.1 page 28, 6.3 page 58, and last but not least 9.4/9.5 on pages 91/92).