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:
- 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
- The Number.Factorial doesn’t exist in the M language yet
- 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:
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
Checkingin
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.
Thank you so much for this incredible article. Amazing!
Thank you!
Great guide! Thanks Miguel!
Thanks, Ivan! Glad that you like it 🙂
Great tutorial!
Thank you very much Miguel!
Glad that you like it!
I decided to learn how to write M languages function for the same bulk replace scenario. Funny enough, I read the factorial part of you blog post and wrote my own code. After I made it work I came back and read the rest of your post just to find out you had provided a solution for that. 😀
My code is nowhere near as clean as yours and I didn’t think of using the list.buffer function. One think I did was adding another step to generate a list of columns with the type text:
ColNames = Table.ColumnsOfType(tbl1,{type nullable text})
I needed it because I had more than one column that needed replacing.
Anyway, great post! It helped me a lot!
Hi,
Loved the function, I allso have to adjust 3 tables, with 4 columns each? So do I need to write this function 12 times or can I use the :
ColNames = Table.ColumnsOfType(tbl1,{type nullable text})
and iff so ? how ?
Kind regards,
Maarten
I’d suggest that you post your full scenario with details and even sample images and files to the official Power Query forum:
https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
cm’on Miguel, I’m in desperate need to deliver a report, the non recursive functions (as in the youtube) takes way to long…
I’m on the brink of mental breakdown…
how can I feed this function a list ???
Translation = fxTranslate (Origen, 0, “Land_PRE”)
so LAND_PRE is the Column name…but i want it to take like 4 Column names…
Please 🙂 I love the M is for data monkey!
I’m not sure I’m following, sorry. You need to do the translation on 4 columns? or you have 4 columns of translations? if you need to translate 4 columns, why not create a single column with a distinct list of values and then do the translation on that one? then you can roll-down those calculations using a simple Merge operation.
Unfortunately I’m a position similar to yours with some hard deadlines and even behind in some of them so I wouldn’t even be able to help you at this time even through consulting work as my earliest availability is mid-September.
If you need this ASAP, I’d recommend getting in touch with my good friend Imke for some urgent consulting work at info@thebiccountant.com – She’s pretty amazing with recursive functions and can help you with exactly what you’re looking for.
its 4 columns that need to be “cleaned” with this function…but no problemo…
I will discuss with my boss to see if there is any budget left 🙂
thanks a lot!
You could apply the function to multiple columns at the same time using Table.TransformColumns
That’s another way to handle it.
Thanks for the idea, but i’m not shure where to wrap this Table.TransformColumns in the provided functions above…
no worries Miguel…I’ll do it manually…thanks again!
Hi,
This is my first time accessing your web site, which I found very interesting. I have a couple of questions regarding Power BI as I am new to it.
First: Should I sign in to Power BI Desktop will I be charged? I am actually using it off line.
Second: How can one use the Jason Power View Themes from inside Power BI? and what are steps to from the Visual Pane to achieve that?
I look forward to your feedback.
Cheers!
Hey! Power BI Desktop is completely free.
I think that it would be a better idea if you post that second question on the official Power BI Community forum:
https://community.powerbi.com/
Ok, I found how to use the JASON Themes and have got the latest update of Power BI, which has significant themes and therefore no need for the JASON Themes.
Cheers!
Chuck
Thank you for this article and for providing the downloadable file. You provided the technique that I could adapt to solve my problem. The data consists of a double linked list of numbers that have a Many-to-Many relationship. My problem is to create a coding where all the numbers in list-A that are linked to list-B and include all of list-B that are linked to other list B numbers until all linkages are found. Simple grouping of list-A and of list-B counts the first level values for each number. It also provides a quality check on the consistency of the data source from which the table was derived. When I complete the solution I will let you know if you wish.
If you would like a copy of the data table let me know by email.
Hey Eugene!
I’d highly recommend that you post your full scenario with sample input and output on the official Power Query forum here:
https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
If you’d like to inquire about our services or request a 1:1 meeting, you can reach us at info@poweredsolutions.co
Best!
Hi Miguel, I have made a query at https://community.powerbi.com/t5/Power-Query/Load-certain-rows-of-a-column-into-the-Buffer-according-to/mp/1342525# M42387
based on your article, and I have been recommended to contact you. Could you give me a hand? thank you!
Hey! The link doesn’t appear to take me to a thread.
If you are looking for any consulting services you can reach directly to us via email at info@poweredsolutions.co
Best!
Hi Miguel,
Have you have reached the recursion limit? I implemented something similar as well, but on 800 / 1000 it throws a stack overflow. And since the last office update it has dropped to 400 or so. Do you know where to check it/ change it?
Thank you in advance
I’d recommend that you post your full scenario on the official power query forum:
https://docs.microsoft.com/en-us/answers/questions/topics/single/139685.html
I haven’t really had that issue before, so I wouldn’t be able to comment on it.
Recursion has a very reasonable limit and that is stack overflow. Each recursive call create a new stack and if you have a long list then it will throw overflow error. So recursion is only good option when you don’t have any other way to do your task.