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 PowerBytes article is for you! Sequence function to the rescue.
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.
I must admit, when I stumbled across this function a few years ago, I had no idea how good it was. When you combine it with other functions, such as ForAll or Char, you really get a lot of value. The Microsoft documentation does give a few examples, I’m hopefully going to give you some better, or more useful ones.
Examples
Here’s my 10 top 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 currently 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
All the credit in the world to the ever-awesome Matthew Devaney for this one. I’ve had his video saved in my browser favourites since he released it, it’s pure gold.
If you currently do this, or something similar to this to generate row numbers:
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(
Last(
FirstN(
colListOfTimes,
Value
)
),
{RowNo: Value}
)
)
)
Alternatively, replace the Last / FirstN method and use the Index function instead:
ClearCollect(
colListOfTimesNumbered,
ForAll(
Sequence(CountRows(colListOfTimes)),
Patch(
Index(
colListOfTimes,
Value
),
{RowNo: Value}
)
)
)
I could go on for ages about the Sequence function and in my opinion, it doesn’t get enough love for how useful it is in every-day Power Fx scenario’s.
Do you have any other awesome uses of the Sequence function? Feel free to whack some examples in the comments below!
