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
7 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Tom
Tom
1 month ago

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

Sergio
Sergio
1 month 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
1 month 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
1 month 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
1 month ago
Reply to  Dean

Got this sorted.