Using a Custom Function Within List.Generate()

Looping with the M language is not easy (Power Query). It's just not intuitive for me. All of the examples I found in my searches were only doing simple loops to iterate an integer from 0 to 10 (or something similar). Rick from BI Gorilla does a great job with an introduction to List.Generate, but he doesn't go into detail about some of the quirks that you may experience.

For example, what if you want to loop a custom function and break out of the loop based on the functions return value? Given the common example we see on the internet, (at least from Microsoft: https://learn.microsoft.com/en-us/powerquery-m/list-generate) it's not easy to see how...

Example 1):

List.Generate(() => 10, each _ > 0, each _ - 1)

Example 2):

List.Generate(
    () => [x = 1, y = {}],
    each [x] < 10,
    each [x = List.Count([y]), y = [y] & {x}],
    each [x]
)

It's not obvious where to put our function... and what are all those brackets!?

Custom Function Implementation

Given a function, "YourCustomFunction()", the syntax to implement your custom function is as follows:

List.Generate( 
    () => [x = 0],
    each [x] < 10,
    each [x = [x] + 1],
    each YourCustomFunction([x])
)

We can see it's actually very similar to the second example above. It still uses the brackets around the integer being iterated, but instead of using List.Count, we simply call our function in the selector.

How to Break Out of the Loop Early?

For my needs, I specifically wanted something like a While loop. For instance, while X equals TRUE continue to loop, where X is a result of YourCustomFunction(). While there's no "While" loop (pun intended), we can achieve similar functionality with the following:

List.Generate( 
    () => 
    [
        // Define variables here
        x = 0, 
        results = 0, 
        condition = true
    ],
    each [condition] = true,
    each [
        x = [x] + 1, // Increment previous x by 1
        results = YourCustomFunction([x] + 1), // Get results
        condition = [results] = 0 // Does the result still equal 0?
        ],
    each [results] // Return the value of results
)

The important thing to understand (and the most confusing part), is the difference between [x] (with square brackets) and just x (without square brackets.

• [x] refers to the previous x value. This CAN be used on the right side of the operation.

• x refers to the current value. This CANNOT be used on the right side of the operation.

For example, this works:

x = [x] + 1

But this will NOT work

x = x + 1

For this reason, if you want your condition to be based on the current iterative value of x, you need to add +1 to it (like shown in the above example)

results = YourCustomFunction([x] + 1)

Otherwise, the condition won't be met until the next loop. Confusing... I know. Perhaps this example will clear some things up.

A Deeper Dive

To see how Power Query M loops iterate and return values, I'd like to demonstrate another example. I have a function called "IsGreaterThanFive" that I will be iterating. It simply returns true if the number is greater than 5, otherwise false.

(x as number)=>
let
    result = x > 5
in
    result // either true or false

I'll set up 3 variables for our loop:

    [
        x = 1, 
        results = false, 
        condition = false
    ],

Even though I set up a variable to store the condition (which is redundant for this example - there is a reason for it though) my condition will actually be [x]<= 10:

    each [x] <= 10,

Here is the logic within the loop - I'm only passing in [x] (which, keep in mind is the previous iteration of x):

    each [
        x = [x] + 1, 
        results = IsGreaterThanFive([x]), 
        condition = [results]
        ]

This outputs the following table - notice how the condition variable does not become true until line 8, and the results variable does not become true until line 7.

List.Generate(): [x] is based on the previous iteration!

This is because variables in square brackets i.e. [condition] and [results] are based on the previous iteration, which is why we see a waterfall-flow in the table above.

However, if we add 1 to [x] for our IsGreaterThanFive function like this:

    each [
        x = [x] + 1,
        results = IsGreaterThanFive([x] + 1),
        condition = [results]
        ]

We will get a table more like what we expect:

List.Generate(): [x] + 1 is what we need

We still see that condition doesn't become true until line 7, but that is because condition is still based on the previous result (condition = [result]), however, condition was a redundancy I set up to demonstrate this point.

In Conclusion:

1. To simply use a custom function in your loop, place it in the fourth selector parameter.

2. Values used in a function need to be wrapped in square brackets [x].

3. Values in square brackets are based on the previous iteration.

Well, I hope that clears things up! Now go on and impress your co-workers. Cheers!