Power Fx – Using the Sequence Function

Even though Power Fx Sequence function has been there for a while, I discovered it myself quite recently while implementing the Planner Gantt Chart app.

Short intro to Sequence(…) function

Basically the function – as the name suggests – generates a sequence of numbers. You must provide the number of elements to generate and optionally provide the starting number the increment value. So instead of using

[1, 2, 3, 4, 5]

in your Power Fx formula you can use

Sequence(5)

Or instead of

[4, 6, 8]

use

Sequence(3, 4, 2)

Note, you can also use a negative and a decimal number as the step parameter.

One might ask that what’s the actual use of the function if you can replace Sequence(3) with simply [1, 2, 3]?

Well, first of all the function supports up to 50000 records. Try hard coding numbers from 1 to 1000 with the […] notation 😂

And, of course, Sequence function provides means to create a dynamically changing table of numbers. So based on your variables or other numeric values in your app you can have the number of records, step value and starting value depend on your app’s logic.

But the true power of Sequence function presents itself once you combine it with ForAll function. I use that combination in various places of my Gantt Chart app’s formulas which I’ll highlight next to give you some idea of different ways to utilize Sequence function.

Examples from the Gantt Chart app

When the Gantt Chart screen (scrGantt) loads, among other things, I run a code block like below.

// Match colors defined in App OnStart to the selected plans
ForAll(AddColumns(Sequence(CountRows(selectedPlans)), "seqIndex", Value),
    Collect(planColors, 
        { 
            colorSchema: First(Filter(colPlanColors, ThisRecord.Value.index = seqIndex)),
            planId: Last(FirstN(selectedPlans, Value)).id
        }
    )
);

In the code block I am collecting records into a planColors collection by iterating a table generated by the Sequence function. The function is provided with a single parameter whose value matches the count of plans the user has selected on the home screen of the app. Iteration is carried out by the ForAll function and the resulting collection contains records having planId and colorSchema.

Later in the app I can then utilize this collection to have plan specific color schemas in the chart. The color schema is defined in app’s OnStart:

ClearCollect(colPlanColors, [
    {
        index: 1,
        indicatorColor: RGBA(0, 0, 255, 1),
        chartColor: RGBA(0, 0, 255, .6)
    },
    {
        index: 2,
        indicatorColor: RGBA(255, 0, 0, 1),
        chartColor: RGBA(255, 0, 0, .6)
    },
    {
        index: 3,
        indicatorColor: RGBA(255, 215, 0, 1),
        chartColor: RGBA(255, 215, 0, .6)
    },
    {
        index: 4,
        indicatorColor: RGBA(75, 0, 130, 1),
        chartColor: RGBA(75, 0, 130, .6)
    }
]);
Set(varMaxSimultaneousSelectedPlans, CountRows(colPlanColors));

To generate the year selector and the month range selector, I use the following code as the gallery’s Items value.

Sequence(4, Year(Now()) - 1)

…to get four selectable years starting from the previous years:

Sequence(12)

… simply to get twelve months:

Then I can get the month name to show like this:

Text(Date(2000, ThisItem.Value, 1), "[$-en-GB]mmm")

Based on the selected month range I calculate the number of days that should be displayed in the chart. Then using that value (stored in a hidden Label lblStartDate) I generate vertical lanes for days as a horizontal gallery having the following code as Items property value:

AddColumns(
    ForAll(
        Sequence(Value(lblNumberOfDays.Text)),
        {
            index: Value,
            date: DateAdd(varStartDate, Value - 1, Days)
        }
    ),
    "isWeekend",
    Weekday(date, Sunday) = 1 || Weekday(date, Sunday) = 7
)

Here I am using the same ForAll + Sequence pattern again. Sequence function generates a table containing numbers from 1 to the number of days and ForAll generates another collection out of this having records containing day index and the date. Finally AddColumns is used to add yet another column to indicate whether the day is weekend. With that as gallery’s data source I am able to render a vertical grid like below.

For month column headers I am also taking advantage of the Sequence function. Headers are rendered as a horizontal gallery with Items:

Sequence(varSelectedEndMonth - varSelectedStartMonth + 1, varSelectedStartMonth)

So, as you can see, I am using the Sequence function in many places in the Gantt Chart app. I would strongly recommend checking out the function docs and start using it if you haven’t yet done so.