Power Community

Power Community

10 Easy Ways To Use The Power Apps Sequence Function!

image

Do you build collections like this:

ClearCollect(
colAlphabet,
["A","B","C","D","E","F","G","H","I","J","K","L","M",
"N","O","P","Q","R","S","T","U","V","W","X","Y","Z"])

If so, this article is for you!

Overview

The Power Apps Sequence function can be used to generate a sequential list of information. This can be numbers, dates, times, random values, or anything else to prevent hard coded logic in your Power Apps. The result of a Sequence function always returns a column called ‘Value’.

The Power Apps Sequence function is constructed like so:

Sequence( Records [, Start [, Step ] ] )

The records property is required; this is the number of records you want created, of which there is a hard upper limit of 50,000. I’d suggest if you want to do something that big, you probably need a reference table somewhere in your data source!

Start is optional. This is the starting number from which you want the sequence to start. The default is 1.

Step is also optional. This is the incrementation for each number in the sequence. This can be a negative number if you want a descending order sequence. Again, the default is 1.

When you combine it with other functions, such as ForAll or Char, you really get a lot of value. The Microsoft documentation gives a few examples, I’m hopefully going to give you some better, or more useful ones. 

Examples
Here’s my top 10 use cases for using the Sequence function.

Alphabet
If you currently do this:

ClearCollect(
colAlphabet,
["A","B","C","D","E","F","G","H","I","J","K","L","M",
"N","O","P","Q","R","S","T","U","V","W","X","Y","Z"])

Try this instead:

ClearCollect(
    colAlphabet,
    ForAll(
        Sequence(26,65),
        Char(Value)
    )
)

 
Numbers in ascending order
If you currently do this:

ClearCollect(
colNumbers,
[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])

Try this instead:

ClearCollect(
    colNumbers,
    Sequence(20)
)

Numbers in descending order
If you currently do this:

ClearCollect(
    colNumbers,
    [20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1]
)

Try this instead:

ClearCollect(
    colNumbers,
    Sequence(20,20,-1)
)

Numbers – odd only
If you currently do this:

ClearCollect(
    colNumbers,
    [1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39]
)

Try this instead:

ClearCollect(
    colNumbers,
    Sequence(20,1,2)
)

Numbers – even only
If you currently do this:

ClearCollect(
    colNumbers,
    [0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40]
)

Try this instead:

ClearCollect(
    colNumbers,
    Sequence(21,0,2)
)

Dates – next 7 days
If you currently do this:

ClearCollect(
    colDates,
    [Today(),Today()+1,Today()+2,Today()+3,Today()+4,Today()+5,Today()+6]
)

Try this instead:

ClearCollect(
    colDates,
    ForAll(Sequence(7,0), Today()+Value)
)

Calendar months
If you currently do this:

ClearCollect(
    colCalendarMonths,
    ["January","February","March","April","May","June",
     "July","August","September","October","November","December"]
)

Try this instead:

ClearCollect(
    colCalendarMonths,
    ForAll(Sequence(12), Text(Date(Year(Today()), Value, 1),"mmmm"))
)

If you want the first 3 letters of each month instead of the full name, remove one of the m’s in the formatting:

ClearCollect(
    colCalendarMonths,
    ForAll(Sequence(12), Text(Date(Year(Today()), Value, 1),"mmm"))
)

Time with hourly increments
If you do this:

ClearCollect(
    colBusinessHours,
    ["08:00 AM","09:00 AM","10:00 AM","11:00 AM","12:00 PM","13:00 PM",
     "14:00 PM","15:00 PM","16:00 PM","17:00 PM","18:00 PM"]
)

Try this instead:

ClearCollect(
    colBusinessHours,
    ForAll(
    Sequence(11),
    Time(8, 60 * (Value - 1),0)
))

Time with half hourly increments
If you currently do this:

ClearCollect(
    colBusinessHours,
    ["8:00 AM","8:30 AM","9:00 AM","9:30 AM","10:00 AM","10:30 AM","11:00 AM",
    "11:30 AM","12:00 PM","12:30 PM","13:00 PM","13:30 PM","14:00 PM","14:30 PM",
    "15:00 PM","15:30 PM","16:00 PM","16:30 PM","17:00 PM","17:30 PM","18:00 PM"]
)

Try this instead:

ClearCollect(
    colBusinessHours,
    ForAll(
    Sequence(21),
    Time(8, 30 * (Value - 1),0)
))

Row numbers
If you currently do this, or something similar to this to generate row numbers for collections:

ForAll(
    colListOfTimes,
    Collect(
        colListOfTimesNumbered,
        Last(
            FirstN(
                AddColumns(
                    colListOfTimes,
                    "RowNo",
                    CountRows(colListOfTimesNumbered) + 1
                ),
                CountRows(colListOfTimesNumbered) + 1
            )
        )
    )
)

Try this instead:

ClearCollect(
    colListOfTimesNumbered,
    ForAll(
        Sequence(CountRows(colListOfTimes)),
        Patch(
            Index(
                colListOfTimes,
                Value
            ),
            {RowNo: Value}
        )
    )
)

Hopefully the above has given you some excellent examples of the power of the Sequence function!
——————————————————————————————————————————
Want more tips like this in your inbox every week? Visit my blog & subscribe: https://platformsofpower.net

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Prompt Like a Pro: Transform your messages with Microsoft Copilot in Teams

Effective and efficient communication is key when it comes to managing your workday. Whether you’re collaborating with colleagues, discussing...

More Articles Like This

- Advertisement -spot_img