Recursive Functions in Power Query
We are all more or less familiar about recursive functions with programming languages such as Python, but what about Power Query? Can we use recursive functions with it? The answer is YES. This post will walk you through an easy example.
An easy example: Factorial Function
The first example involves writing a simple factorial function. The function is shown below:
1 2 3 4 5 6 7 8 9 10 | let Factorial = (x as number) as number => let Source = if x = 1 or x = 0 then 1 else x * @Factorial(x-1) in Source in Factorial |
As you can see, it contains two blocks of let/in. The outer block declares and returns the function, while the inner blocks defines its functionality. The recursive call to the function is done by prefixing the name of the function with a @
Power Query Recursive Function
Download the excel file here: https://1drv.ms/x/s!AiiWkkwHZChHlDa8_zCyu1KkY2nu?e=PEeOyK
A more useful example: Traversing a Hierarchy
While the previous example was a good learning exercise, it was not so useful since Power Query already has a native Number.Factorial function written for you.
Let us look at a better example! Imagine you had the below table and wanted to add a custom column representing the ID of the individual at the very top of the hierarchy. It becomes quite easy using a recursive function:
Power Query Recursive Function
1 2 3 4 5 6 7 8 9 10 | let GetTopID = (t as table, current_id as number) as number => let filtered_table = Table.SelectRows(t, each [ID] = current_id), result = if filtered_table[ID Manager]{0} = null then filtered_table[ID]{0} else @GetTopID(t, filtered_table[ID Manager]{0}) in result in GetTopID |
Adding a custom column using the above function will give us the below results:
Power Query Recursive
Of course you are not limited to this, you can also return a list of IDs in the hierarchy:
1 2 3 4 5 6 7 8 9 | let GetIDList = (t as table, current_id as number) as list => let filtered_table = Table.SelectRows(t, each [ID] = current_id), result = if filtered_table[ID Manager]{0} = null then {filtered_table[ID]{0}} else @GetIDList(t, filtered_table[ID Manager]{0}) & {filtered_table[ID]{0}} in result in GetIDList |
Power Query Recursive Function
Download the file example here: https://1drv.ms/x/s!AiiWkkwHZChHlDh2dgl824bjoCFc?e=waztnP
Conclusions
Learning to use recursive functions in Power Query opens up the door to solving new problems, trying things you may have not thought about before.
Did you find this post useful? Would you like to see more examples? Have you used recursive functions in Power Query before? Please share it and leave a comment down below! See you at the next post!
Hi,
Thanks for the article.
I came across a problem if IDs in hierarchy are not in ascending order (e.g. “1 > 99 > 4” would return corresponding names as if it was “1 > 4 > 99” hierarchy). Is there a tweak to overcome this?
Hi.
Within your example I am trying to add another column based on your custom function but at the point where I need to fill-in the parameters; the “t” parameter has no table value to choose from. Do you know why this would be happening?
Thanks
John