Power Apps Generate Row Numbers In A Collection
Generate row numbers for a Power Apps collection with this formula. Copy and paste this code into your own app.
// Generate row number code
ClearCollect(
colSolution,
ForAll(
Sequence(CountRows(colMyCollection)),
Patch(
Last(FirstN(colMyCollection, Value)),
{RowNumber: Value}
)
)
)
Here’s how it works:
The ForAll function returns a table of values with the row number included. First, we use the the CountRows function to determine how many rows are in the original collection and the Sequence function to generate the row number. Then we Patch the row numbers into the original collection and show them in a new column named RowNumber.
For more help with collections check out my Power Apps Collections Cookbook. There are over 50 examples and code you can copy + paste into your own apps.
Full Example: Generate Row Numbers
Input Collection
Start with this Power Apps collection named colVehicles.
| Year | Make | Model |
| 2016 | GM | Yukon |
| 2016 | GM | Yukon |
| 2016 | GM | Yukon |
| 2017 | Nissan | Frontier |
| 2018 | Chevy | Silverado |
| 2019 | Honda | Ridgeline |
| 2020 | Ford | F150 |
| 2020 | Ford | F150 |
| 2020 | Ford | F150 |
| 2020 | Ford | F150 |
Output Collection
Our goal is to add the row numbers from the Power Apps collection colVehicles and achieve this result.
| RowNumber | Year | Make | Model |
| 1 | 2016 | GM | Yukon |
| 2 | 2016 | GM | Yukon |
| 3 | 2016 | GM | Yukon |
| 4 | 2017 | Nissan | Frontier |
| 5 | 2018 | Chevy | Silverado |
| 6 | 2019 | Honda | Ridgeline |
| 7 | 2020 | Ford | F150 |
| 8 | 2020 | Ford | F150 |
| 9 | 2020 | Ford | F150 |
| 10 | 2020 | Ford | F150 |
Solution Code
Use this code to remove the duplicate rows from colVehicles.
//Create a collection
ClearCollect(colVehicles,
{Year: 2016, Make: "GM", Model: "Yukon"},
{Year: 2016, Make: "GM", Model: "Yukon"},
{Year: 2016, Make: "GM", Model: "Yukon"},
{Year: 2017, Make: "Nissan", Model: "Frontier"},
{Year: 2018, Make: "Chevy", Model: "Silverado" },
{Year: 2019, Make: "Honda", Model: "Ridgeline"},
{Year: 2020, Make: "Ford", Model: "F150"},
{Year: 2020, Make: "Ford", Model: "F150"},
{Year: 2020, Make: "Ford", Model: "F150"},
{Year: 2020, Make: "Ford", Model: "F150"}
);
// Generate row number code
ClearCollect(
colSolution,
ForAll(
Sequence(CountRows(colVehicles)),
Patch(
Last(FirstN(colVehicles, Value)),
{RowNumber: Value}
)
)
)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 about Power Apps Generate Row Numbers In A Collection 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.
Thanks Matthew Devaney, Also, if i use sort function then RowNumber on gallery is not correct. Can you help me?
Hi Matthew,
first of all, thanks for all your solutions.
Regarding your code, is it possible to replace ‘Last(FirstN(colVehicles, Value))’ with ‘Index(colVehicles, Value))’?
Ciao, Sergio
Sergio,
Yes you can replace Last FirstN with Index.
Thanks for sharing this article, Matthew!
Wouldn’t it be easier to use the Index function instead of Last(FirstN?
// Generate row number code
ClearCollect(
colSolution,
ForAll(
Sequence(CountRows(colVehicles)),
Patch(
Index(
colVehicles,
Value
),
{RowNumber: Value}
)
)
)
Dominik,
I agree, using Index() is better
I’m trying to apply row numbers to a table that ultimately gets grouped. I would like assets to have a row number and observations in the same table to have separate row numbers. For example my final collection would have three assets with an added column using row numbers of asset 1,2,3. The grouped data would also have row numbers applied. For example Asset 1 could contain Observation 1,2,3. Asset 2, Observation 1, Asset 3, Observation 1,2…etc. I’ve been struggling to achieve this.
Got this sorted.
Hi Dean,
would you mind sharing the fix/solution ?
thank you
Your ‘non-index’ formula leads me to conclude: The first Value in the ForAll pulls make, model, year (ie entire existing record) into new collection’s record and second Value for RowNumber pulls a number. I hope I can remember these two distinct results for Value (if my analysis is correct) and use it to advantage later on. For sure need to consider Index further too.
I am using your method, Matthew, along with Scott Durow’s drag drop component to build a scheduling app that updates the row number (assigned to a collection of names sorted alphabetically) with a new dropped position number when the name is moved and dropped into a custom non alphabetical spot. These new values will get saved and then recalled to create a collection onStart that replicates a previously saved configuration.
Thanks for the blog post and video resources – much appreciated.
Is it possible to do this without creating another collection?
J,
You could use the table-shaping code from this article directly inside of a control. No need to store it in a collection.
ForAll(
Sequence(CountRows(TableName)),
Patch(
Last(FirstN(TableName, Value)),
{RowNumber: Value}
)
)
Sorry for not making it clear, I already have a collection and just want to add a column to it with row numbers, but without creating another collection – is it possible? In other words, I want to add a column to my existing collection and populate it with row numbers.
This was super helpful. Thank you