Power Apps SEQUENCE Function
Yesterday the SEQUENCE function was announced on Microsoft’s official Power Apps blog. I thought I’d provide some details on how to use SEQUENCE since the Power Apps documentation does not yet include a page for it.
Description
The SEQUENCE function creates a range of numbers as a single column table. This could be a continuous range like the numbers from 1-to-100 or non-continuous range such as [2, 4, 6, 8 10]. Ranges of dates and letters can be produced as well.
Syntax
Sequence(records, start, step)
- records – Required. Quantity of numbers in the sequence.
- start – Optional. Starting number of the sequence. Default is 1.
- step – Optional. Incremental value. Default is 1
Examples
Numbers
A table with all numbers from 1-to-10
Sequence(10)| Output: [1,2,3,4,5,6,7,8,9,10] |
A table with all multiples of 10 from 0-to-100
Sequence(10, 0, 10)| Output: [0,10,20,30,40,50,60,70,80,90,100] |
A table with numbers from 10-to-1
Sequence(10, 10, -1)| Output: [10,9,8,7,6,5,4,3,2,1] |
Letters
A table of all letters A-to-Z
ForAll(Sequence(26, 65, 1), Char(Value))| Output: [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] |
Dates
A table of the next 7 days starting 2020-01-01
ForAll(Sequence(7), Date(2020, 1, Value))| Output: [1/1/2020, 1/2/2020, 1/3/2020, 1/4/2020, 1/5/2020, 1/6/2020, 1/7/2020] |
A table of the next 5 Saturdays starts 2020-01-04
ForAll(Sequence(5, 4, 7), Date(2020, 1, Value))| Output: [1/4/2020, 1/11/2020, 1/18/2020, 1/25/2020, 2/1/2020] |
A table of the first day of each month in the year 2020
ForAll(Sequence(12), Date(2020, Value, 1))| Output: [1/1/2020, 2/1/2020, 3/1/2020, 4/1/2020, 5/1/2020, 6/1/2020, 7/1/2020, 8/1/2020, 9/1/2020, 10/1/2020, 11/1/2020, 12/1/2020] |
A table of the last day of each month in the year 2020
ForAll(Sequence(12), DateAdd(Date(2020, Value, 1), 1, Months) - 1)| Output: [1/31/2020, 2/29/2020, 3/31/2020, 4/30/2020, 5/31/2020, 6/30/2020, 7/30/2020, 8/31/2020, 9/30/2020, 10/31/2020, 11/30/2020, 12/31/2020] |
A table of all of the days in the month of January for the year 2020
ForAll(Sequence(Day(DateAdd(Date(2020, 1, 1), 1, Months)-1), 1, 1), Date(2020, 1, Value))| Output: [1/1/2020, 1/2/2020, 1/3/2020, 1/4/2020, 1/5/2020, 1/6/2020, 1/7/2020, 1/8/2020, 1/9/2020, 1/10/2020, 1/11/2020, 1/12/2020, 1/13/2020, 1/14/2020, 1/15/2020, 1/16/2020, 1/17/2020, 1/18/2020, 1/19/2020, 1/20/2020, 1/21/2020, 1/22/2020, 1/23/2020, 1/24/2020, 1/25/2020, 1/26/2020, 1/27/2020, 1/28/2020, 1/29/2020, 1/30/2020, 1/31/2020] |
Random
A random number between 1-and-100
First(Shuffle(Sequence(1, 100, 1))).Value| Output: 67 (changes to another random number each time) |
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
Questions?
If you have any questions or feedback about the Power Apps SEQUENCE function please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
Hi Matthew,
I got the validation for duplicate date range to work on the calendar app that I’m building. I tested the sequence function separately to create recurring meetings and it worked but when I combined it together, it does create recurring meeting but it stopped validating for conflict date range.
My SharePoint list has StartDateTime and EndDateTime column. I set the Start and End as below and from there set the conflict as varRecord and if it’s empty it should patch with the sequence function. If it’s not blank, then I should get notification error.
Set(Start, start.SelectedDate + stime.Selected.Value);
Set(End, end.SelectedDate + etime.Selected.Value);
Set(varRecord, LookUp(‘Anchorage Working Calendar’, StartDateTime <= Start && EndDateTime > Start || StartDateTime > Start && EndDateTime <= End || StartDateTime < Start && EndDateTime > End));
If(IsBlank(varRecord.ID),
ForAll(Sequence(Value(‘days/weeks’.Text)) As recurringmeetings,Patch(‘Anchorage Working Calendar’, {‘Client Name’: Client.Text, StartDateTime: start.SelectedDate+(recurringmeetings[@Value]*7)+stime.Selected.Value, EndDateTime:end.SelectedDate+(recurringmeetings[@Value]*7)+etime.Selected.Value})),
Notify(“Date Conflict, Booking already exist for selected date and time!”, NotificationType.Error));
Not sure what I’m doing wrong 🙁 Any advise please. Thanks!
Hi,
i have to create a dropdown list, with dates 1st-13th,11th-23rd,21st-03rd of all the months in a year,for eg:- jan month should have dates as 1-01-year to 13-01-year, 11-01-year to 23-01-year,21-01-year to 31-01-year, same has to be for all the months of a year, can you please guide me in this sir.
Nidheesh,
Why do some of the date ranges overlap?
Jan 1st-13th,
Jan 11th-23rd,
Jan 21st-3rd
Each of these overlaps by 2 days.
Yes sir it is one of my requirement for time sheet, like from 1st-13th is the locking period for submission of the timesheet, user can apply time sheet only on the above dates,so is their any way to achieve this dates in the drop-down sir?
Nidheesh,
Can you please give a sample list of date ranges for January to March in a list format?
Sure sir,
01-01-2022 to 10-01-2022
11-01-2022 to 21-01-2022
22-01-2022 to 30/31-01-2022
01-02-2022 to 10-02-2022
11-02-2022 to 21-02-2022
22-02-2022 to 28/29-02-2022
01-03-2022 to 10-03-2022
11-03-2022 to 21-03-2022
22-03-2022 to 30/31-03-2022
Sir requirement is that user can submit time sheet of 1st to 10th on 13th of the month,likewise 11th to 21st on 23rd of the month and
22nd to 30/31 on 1st of the month so 13th,23rd and 1st are the days where he can submit the time sheet sir
Can you please guide me on this requirement sir
Sir can you please guide me on how to achieve the above requirement,it would be of great help
Nidheesh,
Here’s a formula I used to create a collection with to and form dates:
ClearCollect(
colDateRange,
Ungroup(
With(
{
varStartDate: Today(),
varMonthsIntoFuture: 3
},
ForAll(
Sequence(varMonthsIntoFuture, 0),
Table(
{
FromDate: Date(
Year(varStartDate),
Month(varStartDate) + Value,
1
),
ToDate: Date(
Year(varStartDate),
Month(varStartDate) + Value,
10
)
},
{
FromDate: Date(
Year(varStartDate),
Month(varStartDate) + Value,
11
),
ToDate: Date(
Year(varStartDate),
Month(varStartDate) + Value,
21
)
},
{
FromDate: Date(
Year(varStartDate),
Month(varStartDate) + Value,
22
),
ToDate: Date(
Year(varStartDate),
Month(varStartDate) + 1 + Value,
0
)
}
)
)
),
“Value”
)
)
Hey, Can we generate auto serial no.s in Data Table?
Mhk,
Here’s a link to the article I wrote on how to generate row numbers in a table:
https://www.matthewdevaney.com/power-apps-generate-row-numbers-in-a-collection/
How do you use sequence but format it to have leading zeros? I want to have the numbers formatted to 5 digits (i.e. 00003, 00004, 00005, etc.)
Doug,
You must use the TEXT function to format the number with leading zeros.
Concat(Sequence(10), Text(Value, “00000”)&”, “)
Thanks Matthew. I tried that and it actually added the zeros to the end(?) I’ll try it again in the event I typed something wrong but I ended up having a ForAll loop spin through the collection and was able to use the Text formatting there.
Thanks for the quick reply!
Doug,
I made sure to test the solution out so I’m pretty sure it will work. Good luck 🙂
Hi Matthew,
I am trying to get a running count the number of items in a column. Example:
Item Qty
Pencil 1
Book 1
Paper 1
Book 2
Notepad 1
Paper 2
Book 1
Lunch 1
Pencil 2
I do not want to group these items.
Kendall,
Here’s my articles on running totals?
https://www.matthewdevaney.com/power-apps-running-totals-in-a-gallery
Hi Matthew,
I have a for all function which will create new records based on the value selected in the dropdown, if i select 3 in the dropdown then it will create 3 new records.
So i want to patch the alphabets as well for the new records created
as in the first record will have A, then next B and C.
ForAll(Sequence(varlinerepetation),Patch(‘sharepointsource’,Defaults(‘sharepointsource’),{Number:Dropdown3_3.Selected.Value,PES:”A”}))