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.

YearMakeModel
2016GMYukon
2016GMYukon
2016GMYukon
2017NissanFrontier
2018ChevySilverado
2019HondaRidgeline
2020FordF150
2020FordF150
2020FordF150
2020FordF150



Output Collection

Our goal is to add the row numbers from the Power Apps collection colVehicles and achieve this result.

RowNumberYearMakeModel
12016GMYukon
22016GMYukon
32016GMYukon
42017NissanFrontier
52018ChevySilverado
62019HondaRidgeline
72020FordF150
82020FordF150
92020FordF150
102020FordF150


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}
        )
    )
)





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.

Matthew Devaney

Subscribe
Notify of
guest

15 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Tom
Tom
2 years ago

Thanks Matthew Devaney, Also, if i use sort function then RowNumber on gallery is not correct. Can you help me?

Sergio
Sergio
2 years ago

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

Dominik Petri
Dominik Petri
2 years ago

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}
)
)
)

Dean
Dean
2 years ago

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.

Dean
Dean
2 years ago
Reply to  Dean

Got this sorted.

Kerim
Kerim
11 months ago
Reply to  Dean

Hi Dean,

would you mind sharing the fix/solution ?

thank you

Gerald Dahl
Gerald Dahl
1 year ago

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.

J...
J...
8 months ago

Is it possible to do this without creating another collection?

J...
J...
8 months ago

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.

Jason Ciani
Jason Ciani
7 months ago

This was super helpful. Thank you

Shumail
Shumail
4 months ago

it has large impact on performance if you 2000 records and many columns in that collection it get stuck on while doing this process.

carlos
carlos
5 days ago

Sanity-saving site if there ever was one..

Is there any way to subset the count by group? For example, instead of simply going down each row (each becoming 1, 2, 3,..,N), the “indexing” occurs by some grouping from another column:

RowNumber, Value
1 , ValueA
2 , ValueA,
3 , ValueA
1 , ValueB
2 , ValueB
4 , ValueA