cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Generating number to each row in collection

Hello awesome powerapps community!

I have tried different approaches to update an existing collection with a sequential number for each row of the collection.
Fx row 1 = 1
row 2 = 2,
row 3 = 3 etc.
But i can't get it work 😞


The collection i would like to update with a number sequence column is called "ReadyforExport"

Following syntax is one of my attempts:

ForAll (ReadyforExport, 
   Collect(ReadyforExport, 
     Last(FirstN(AddColumns(ReadyforExport, "RowNumber", CountRows(ReadyforExport)+1),
        CountRows(ReadyforExport)+1
         ) 
     )
   )
)



Are there a wonderful soul out there, who can help me solving this?

Have a great weekend

Best regards
Ajazz

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous 

If you collect the output into a new collection (for example, ReadyforExportNumbered), the formula should then hopefully work for you.

ForAll (ReadyforExport, 
   Collect(ReadyforExportNumbered, 
     Last(FirstN(AddColumns(ReadyforExport, "RowNumber", CountRows(ReadyforExportNumbered)+1),
        CountRows(ReadyforExportNumbered)+1
         ) 
     )
   )
)

Once you've generated the RowNumber column with the formula above, you could replace your ReadyforExport collection with your  ReadyforExportNumbered collection, if required.

ClearCollect(ReadyforExport,ReadyforExportNumbered) 

 

View solution in original post

7 REPLIES 7
mdevaney
Super User
Super User

@Anonymous 

Take a look at this article written by forum member (and Flownaut) @timl You should be able to adapt the technique to your scenario.  http://powerappsguide.com/blog/post/generating-row-numbers

 

Tim's a pretty knowledgeable dude.  He even wrote one of the first books about PowerApps.

https://www.amazon.com/Tim-Leung/e/B0065L2JD4%3Fref=dbs_a_mng_rwt_scns_share

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Thanks very much @mdevaney 🙂

@Anonymous - if you're still stuck after reading that, post back here and I'm sure one of us can help. 

Anonymous
Not applicable

Thanks @mdevaney & @timl,

I found that blog before posting, but somehow I can still not get it to work.

This is my syntax as a result of following his guide:

ForAll (ReadyforExport, 
   Collect(ReadyforExport, 
     Last(FirstN(AddColumns(ReadyforExport, "RowNumber", CountRows(ReadyforExport)+1),
        CountRows(ReadyforExport)+1
         ) 
     )
   )
)


Can you experts spot my errors?

Hi @Anonymous 

If you collect the output into a new collection (for example, ReadyforExportNumbered), the formula should then hopefully work for you.

ForAll (ReadyforExport, 
   Collect(ReadyforExportNumbered, 
     Last(FirstN(AddColumns(ReadyforExport, "RowNumber", CountRows(ReadyforExportNumbered)+1),
        CountRows(ReadyforExportNumbered)+1
         ) 
     )
   )
)

Once you've generated the RowNumber column with the formula above, you could replace your ReadyforExport collection with your  ReadyforExportNumbered collection, if required.

ClearCollect(ReadyforExport,ReadyforExportNumbered) 

 

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want to add a Row Index column within your existing ReadyforExport collection?

 

Based on the formula you provided, I think there is something wrong with it. I have made a test on my side, please consider take a try with the following formula (set the OnSelect property of a button to following) :

Clear(FinalCollection);
ForAll(
       ReadyforExport,
       Collect(
               FinalCollection,
               {
                 RowNumber: CountRows(FinalCollection) + 1,
                 Column1: ReadyforExport[@Column1],     // Specify same column names as that from your ReadyforExport collection within your FinalCollection
                 Column2: ReadyforExport[@Column2],
                 Column3: ReadyforExport[@Column3],
                 ...
               }
       )
)

Then you could use the FinalCollection as data source within your app instead of the original ReadyforExport collection.

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I can confirm the approach demonstrated by @v-xida-msft solves this problem, adding a RowNumber column to the collection, starting at 1 and incrementing up to as many records as contained in the collection.

 

It seems as though there is probably a way to do this without restating all the fields but in my case there were not too many fields and this approach produced an easy to understand progression that is readable and makes sense.

 

Thanks!

 

Reigningfear
Advocate IV
Advocate IV

Hello - I realize this is an old thread but wanted to share another alternative which might be helpful. I've been using @mdevaney 's solution which has worked great but I ran into some weird Schema errors (probably my fault but couldn't fix it). Instead, I experimented with 'UpdateIf' and came up with the code snippet below:

UpdateIf(YourCollection, 1=1 /*update all rows*/,
            {
            RowNumber:
                If(
                    //If no rows have a RowNumber, Let's make the first row a 1.
                    IsEmpty(Filter(YourCollection,Not(RowNumber = Blank()))), 1,
                    //Gets the latest (biggest) row number and adds 1 to it.
                    Last(Filter(YourCollection,Not(RowNumber = Blank()))).RowNumber + 1
                )
            }
            
        );

 This has been working for me but I admit it's a recent discovery for me so unsure if there are any holes in this. One thing to note is I did need to add the column to the collection prior since this isn't part of the default data source; YMMV. 

 

Hope this helps!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,165)