Intro from Imke: “I’m very proud to announce the 1st guest post on my blog written by Daniil Maslyuk. His Twitter slogan: “I am a fan of Power BI and avocados” made it instantly clear to me that this young man has an excellent eye for the essence: Just what it takes to write good blog posts. So I asked him to write a guest post on my blog. As it turned out, he was just about to start his own (XXLBI.com) which many of my readers will know already: It contains some very elegant DAX off the beaten track and is a real pleasure to read. So I’m very happy that he agreed to publish with me as well:”

When I was a school student, a teacher asked me if I knew what my life path number was.

- What’s life path number?
- It’s the number you get when you sum the digits of your birth date again and again until you get a single-digit number

Example: if you were born on 25 December 1963, your life path number would be 2:

- 2 + 5 + 1 + 2 + 1 + 9 + 6 + 3 = 29
- 2 + 9 = 11
- 1 + 1 = 2

Now, I’m not a big fan of numerology, but I am a big fan of Power Query. What does the former have to do with the latter? Life path numbers are calculated recursively, and you can totally do it in Power Query! In this article, I am going to introduce you to recursion in M.

### The easy (but not the good) way

First, I am going to show you the easy way. Let’s start with a blank query:

Rename the query to MyFunction, then open Advanced Editor and paste the following code:

(Code to download: Code_Recursion_M_Daniil.txt )

This is a custom function that takes one parameter – MyNumber – a number specified by user. The function starts with MyNumber and doubles it until the number becomes greater than 10.

How exactly does it work? First, we are checking whether the number we start with is already greater than 10 (red part), and if it is, then we stop there (green part). If it’s less than or equal to 10, then we call the function from itself by using @ in front of its name (blue part), but this time with the number doubled.

Example:

- Start with 1
- Is 1 > 10? No, multiply by 2: 1 * 2 = 2
- Is 2 > 10? No, multiply by 2: 2 * 2 = 4
- Is 4 > 10? No, multiply by 2: 4 * 2 = 8
- Is 8 > 10? No, multiply by 2: 8 * 2 = 16
- Is 16 > 10? Yes, return the result: 16

You can confirm this if you invoke MyFunction with 1 as parameter:

Doesn’t look hard at all, does it?

### The good (but not so easy) way

As Chris Webb found out, recursive calculations, for reasons outside of scope of this blog post, should be done with the function called List.Generate instead of the @ syntax. List.Generate has the following syntax:

List.Generate (

start value,

condition to check,

what to do in case the condition is true,

(optional) what to do with the results

)

The logic here is slightly different from the @ syntax we used before:

- You specify the value you start with (red part)
- The value is checked against a condition (green part)
- Transformations are applied until the condition is not true (blue part)
- Optionally, you can apply further transformation to the results (grey part)

All the results are stored in a list – List.Generate, as the name implies, returns a list.

Note that optional transformations (grey part) are not considered when the condition is checked (green part). In other words, they are applied once the final list of results is generated.

To reconstruct our previous recursive function with List.Generate, we need to write the following:

Invoking the function with 1 returns 16 just as in the previous case. Notice how we must pick the last member of the list with List.Last, because List.Generate returns a list.

### Performance considerations

If you feed a table or list to List.Generate, you may want to wrap them in Table.Buffer or List.Buffer, respectively. This way the table or list in question will not be referenced every time List.Generate evaluates its values, resulting in better performance. In a way, the two functions work similarly to VAR in DAX – they too evaluate an expression only once.

### Calculating your life path number

So how do you calculate your life path number with Power Query? Here is one way:

Here’s what we are doing:

- Convert your birth date to an integer
- Create an inline function that sums digits
- Convert the number to text
- Break the text string apart by digits
- Convert each digit from text to number
- Sum all the digits

- Sum the digits of your birth date recursively until we get a single-digit number
- Pick the last result from List.Generate

If you are confused when you see fSumDigits by itself, please note that it corresponds to the following syntax:

each fSumDigits ( _ )

In other words, if a function takes just one parameter, and you are applying it to each item in a list, you can write your code more concisely by just referencing the function name.

My life path number is 2. What’s yours? 🙂

Excellent one ! Congrats 🙂

M is so powerful

Hi Imke and Daniil! Nice explanations and funny example 🙂

This technique is great for pulling records from a cloud service in say pages of 20 records at a time. Recursion simply lets you keep grabbing without needing to ask how many in total.

Good observation, David — that’s a very good way to use List.Generate!

Just need to be careful with Web.Contents in case you are using it 🙂 More details here: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

Great blog post. Thanks!

I cannot thank you enough for this post. I had been struggling to understand how to recursively apply M user-defined functions to iterate a series of text replacements over a text block. MyFunction showed me the way — it is my Rosetta Stone. The official M documentation is like ancient Sumerian tablets — cryptic and sparse. Thank you, thank you, thank you!

I only take issue with you on one thing — the easy way is far and away the best way!

Here’s the set of text replacements I needed to do (2 columns, find and replace):

DescTransformSteps

find replace

.

.

: :

.

”

” .

.

.

;

”

”

ascii(10)

And here’s the function to iterate through them in order:

let

DescTransform = (params as list) =>

// params = {text_input, steps[find, replace], n} where:

// params{0} = text_input = passed state of the text being transformed;

// params{1} = steps[find]{n} string to find; steps[replace]{n} string for replace; and

// params{2} = n = current index value for steps{find] and steps[replace], 0-based.

if params{2} > Table.RowCount(params{1}) -1

then params

else @DescTransform( // recursive call to self until all rows in steps have been processed

{

Text.Replace(params{0}, params{1}{params{2}}[find], params{1}{params{2}}[replace]),

params{1},

params{2} + 1

}

)

in

DescTransform

Elegant and spare and works like a charm!