Recursive Functions in Power BI / Power Query

Have you ever heard about Recursion or Recursive functions?  They are present in the M language for Power BI / Power Query and this is a post where I’ll go over how to use recursion or make recursive functions in Power BI / Power Query.

This is a pretty advanced topic on Power BI / Power Query and the M language.

I’ve tried my best to make this a simple read, but I do highly recommend that you read my previous posts on “Parameters and Functions” ( 1 | 2 | 3 | 4 ) , “Conditional Logic” ( 1 | 2 | 3 ) and the Column Navigation post before reading this article.

What is recursion?

In short, recursion is a method of solving a problem where the solution depends on solutions to smaller instances of the same problem.

The way that this happens is that within Power BI / Power Query, a function can call itself within its own code using the @ sign.

This is incredibly powerful from a performance perspective in contrast with something like iterations (like the List.Generate function).

Let’s look at a Technical Example first and then we’ll go with a more practical example for recursion in Power BI / Power Query.

Technical Example

In Power Query, or the M language, there’s a function called Number.Factorial which does a factorial as showcased on this wiki page.

Let’s imagine this scenario for a second:

1. You are currently working at Microsoft side by side with Curt, Matt, Miguel Ll., Ehren, and the rest of the folks that work on Power Query
2. The Number.Factorial doesn’t exist in the M language yet
3. You’ve been tasked with the mission of creating a new M Factorial function from existing M functions

How would you make that happen?

You have 2 ways to make that happen and the easiest one would be to use a function by the name of List.Accumulate:

Now, that function works and will give the correct results, but it looks more like a wrapper, because it doesn’t really tell you what’s going on behind the scenes.

Only the Microsoft folks know exactly what happens behind the scenes of that List.Accumulate function, so we want to go one step further and take a more explicit approach.

For that, we’ll be creating our very own recursive function which has the following script:

The first step, called Source, of that function just creates a continuous list of numbers from 1 through whatever the input (y) is.

The second step, called Count, just gives us a total (count) of the elements in that list.

I want you to pay close attention to what happens from lines 5 through 10 where I define the third step (actually a function) by the name of fxFactorial.

This function takes 3 arguments:

• A list of numbers (x)
• A number which acts as a counter (n)
• An initial number for the Factorial (initial)

The logic goes like this, we pass the list that we created in the Source step to the first argument of this function, the second argument will be 0 (the counter will start from 0) and the last argument will be null.

That’s why you see the last step of the whole function to be:

Custom1 = fxFactorial(Source,0, null)

What will happen inside the function is that it’ll try to figure out if the initial argument is set to null, if it is then it’ll multiple the first element of the list by 1.

In our case, that’ll be multiplying the number 1 by 1.

The second step of that function simply checks which elements of the list were used for this calculation and to check if we should continue with the next element (if there’s any) or not. That’s the line #8 where I use the if statement and use the @  – the @ is the crucial part because that’s where I’m recursively calling that function based on that if statement, but notice that this time the arguments of that function are different.

I’m still passing the same list as the first argument, but the second argument is “n+1” and remember that n was 0 initially, so now it’ll be 1. That n is essentially a counter and the last argument is the result of the previous Calculation, hence why I called that step as Calculation and the parameter is called “initial”.

Again, the crucial part of it all is making a calculation on a per element basis and taking the result of a previous calculation into account for a new calculation.

That’s why we use the @ (at) sign in Power Query – it’s used for recursion and it’s a programming method within the M language that allows a function to call itself when needed.

Putting this into action, you’ll see that when you call this function, the result is correct. In my case, I tested this with the 5!:

Practical Example

The previous example is something purely technical that you might never see in the real world, but it does give you a bit of theory and background as to why, from a programming perspective, recursion is needed and how it is already implemented in some M functions for you.

Recursion is usually not something that I do. In fact, in my 5 years using this tool, I’ve only needed recursion 3 times and I’m about to show you one of those scenarios which was actually showcased to me by my good friend Bill Szysz (YouTube).

A few years ago I published this video: