Loops! Part 1: Sequence

19 Mar, 2021

Loops are exactly the reason why you would want a computer to do work for you. Just like Mickey Mouse in the Sorcerer's Apprentice, all we want to do is to get our brooms to do the repetitive mopping for us, instead of doing it by hand.

Looping is one of the first things that you learn when you learn a 'real' programming language, but it may not be the first thing you learn when you're doing Power Apps or Power Query. Looping is certainly possible in both of these. Let's explore some concepts.

Power Apps

Power Apps doesn't have a for keyword, but it does have the ForAll() function.

ForAll() is very versatile. It can return a table, or a series of commands, or combined with Concat() to return a string, and perhaps more practical things that I'm mentioning right now.

That can make it a bit slippery to get a mental handle on.

I have a few useful patterns that I go back to, which make it easier for me to handle it.

n-th item access

In JavaScript, we have the handy [i] operator to get to the i-th member of an array.

In Power Apps, it's a little more verbose. I usually use the combination Last(FirstN()). This gives me the last of the first "N" rows in a table, which in effect, gives me the N-th row in the table. The usefulness of this will come into light in later sections.

ForAll() with Sequence()

The default case for looping in a 'normal' programming language is to loop trough an array, for the length of the array. In this case, you usually have access to the index.

For example, in JavaScript, you can set up a for loop with an index i, and have access to the i-th item of the array you're looping through.

With Power Apps, it takes an extra step, using Sequence().

But why?

Feel free to skip over to the next section if you're interested in just the method, but this is the reason why I discovered this technique in the first place.

Firstly, why bother doing this? Why can't we just do what we want to do using ForAll()? A very good reason is that you can't ForAll() through a table and perform Patch()s on that table at the same time.

Let's say that you think that this a sensible thing to do, with the table tblData.

ForAll(
    tblData,
    Patch(
        tblData,
        Value,
        {
            fieldName: "New value"
        }
    )
)

You want to patch every single value in the column fieldName with "New value". It looks like the Value will pick up from the current record being looped on within tblData.

Power Apps won't accept this. The workaround is to use Sequence()

The method

Sequence() produces an array of consecutive numbers up to the argument.

I usually use it with CountRows(). This works just like doing a for loop until array.length in JavaScript.

It looks like this:

Sequence(
    CountRows(
        tblData
    )
)

Let's say there are 10 rows in tblData. The block above will produce this array:

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

This is only good to us if we have field access. Luckily, we know how to do this.

To amend the ForAll() loop above:

ForAll(
    Sequence(
        CountRows(
            tblData
        )
    ),
    Patch(
        tblData,
        Last(FirstN(tblData, Value)),
        {
            fieldName: "New value"
        }
    )
)

This block will successfully patch every row of the table tblData.

#powerapps