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:

About the Scenario: Bulk Replace Text Strings

In Power BI / Power Query, whenever you want to replace a text string inside of a column, you need to do it one at a time. Imagine that you have 10 replacements that you need to do, that’ll translate into 10 times that you’ll have to click on the “Replace values” button and go through the configuration menu:

The idea is to make this completely automatic so it can be done in just 1 step and save us time.

To make that happen, we have 2 tables:

  • Our Source table – that has a column that contain text strings that we want to replace

  • A translation table – that holds value pairs. One for the [OldText] and another value for the [NewText] that should replace the oldtext.

In my original attempt, I used an iteration process with List.Generate, but it wasn’t optimal when dealing with vast amounts of rows, so a better approach was found by Bill with a recursive function.

Let’s look at this case deeper. You can follow along by downloading the completed file from the button below:

Download File

Step 1: Load the Tables

The first thing that we need to do is load both tables from this workbook into Power Query as connection only. You can use the file to connect directly to it as an excel table / range or connect through a blank Workbook or Power BI Desktop file.

The goal is to have both of the previously mentioned tables loaded.

Step 2: Buffer the OldText and NewText Columns

We’re going to create a new query and in it we’ll be buffering the columns from our TranslationTable.

The main reason why we do this it’s for performance purposes. Buffering will make sure that we have those columns available at a really quick pace.

For that, our query will start like this:

let
Origen = Tareas,
Old = List.Buffer(TranslationTable[OldText]),
New = List.Buffer(TranslationTable[NewText])

in

New

In this query we’re just loading the Table that has the column “Tarea”, and then we have buffered the columns of the translation table separately.

Step 3: Create the recursive function

Now we need to stitch it all together and create our recursive function. Our method will be to create a new step by the name of fxTranslate and the code for that step will be the following:

fxTranslate = (x as table, n as number, ColName as text ) as table =>

       let

           Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
in
Checking

Let’s look at this code deeper.

The parameters of this function are:

  • x – this is the input table for the function
  • n – remember that this is similar to the previous example. This is just a counter
  • ColName – this is where we input the name of the column inside the x table that we want to replace the text strings

Now let’s look at both of the steps in that function:

  • Replace – this is where we use the Table.ReplaceValue function. We will pass the table and then we’ll be using references to the elements of the columns from the TranslationTable. Remember that we added 2 steps (Old and New) and buffered the results within those 2? this is where we use them and finally we also pass the name of the column from the x table that needs to be used for the replacement.
  • Checking – this is where we use some conditional logic to know if we want to keep using recursion or not. It’s all based on the counter. Remember that the counter will start from 0, and 0 will retrieve the first element from the Old and New steps. We compare that counter against the total elements inside the Old step (which is a list) and as long as we haven’t gone through all the elements of that list, we’ll keep doing the recursion and that’s why we have the @fxTranslate(Replace, n+1, ColName). This is where the recursion gets defined

Up until now, our code should look like this:

let
Origen = Tareas,
Old = List.Buffer(TranslationTable[OldText]),
New = List.Buffer(TranslationTable[NewText]),
fxTranslate = (x as table, n as number, ColName as text ) as table =>

                let

                    Replace = Table.ReplaceValue(x, Old{n}, New{n}, Replacer.ReplaceText,{ColName}),
Checking = if n = List.Count(Old)-1 then Replace else @fxTranslate(Replace, n+1, ColName )
in
Checking

in

fxTranslate

Step 4: Invoke the recursive function against the table

Now that we have the function and all the pieces in our query, we can invoke the function as the last step so our code will end up looking like this:

Conclusion

Now you’re probably wondering, when should you use recursive functions? and that’s a tough question! it really depends on your scenario and if you absolutely need to work have a function that works recursively or not. Most advanced users try to go towards List.Accumulate or a List.Generate approach, but sometimes the only optimal approach is to create a recursive function and it truly depends on your scenario.

6 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.