Guest Post: Recursion in M for beginners

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:

1.       2 + 5 + 1 + 2 + 1 + 9 + 6 + 3 = 29
2.       2 + 9 = 11
3.       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:

1. Start with 1
2. Is 1 > 10? No, multiply by 2: 1 * 2 = 2
3. Is 2 > 10? No, multiply by 2: 2 * 2 = 4
4. Is 4 > 10? No, multiply by 2: 4 * 2 = 8
5. Is 8 > 10? No, multiply by 2: 8 * 2 = 16
6. 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:

1. You specify the value you start with (red part)
2. The value is checked against a condition (green part)
3. Transformations are applied until the condition is not true (blue part)
4. 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:

1. Convert your birth date to an integer
2. Create an inline function that sums digits
   1. Convert the number to text
   2. Break the text string apart by digits
   3. Convert each digit from text to number
   4. Sum all the digits
3. Sum the digits of your birth date recursively until we get a single-digit number
4. 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? 🙂