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

Adding an index column to a collection

I have a simple collection, Col_Table1, as follows:

 

Building Element Name:              Project Name

Superstructure                              Building Refurbishment in Michigan

Superstructure                              New Build in Kansas

Superstructure                              Extension in Arizona

 

I want to add an 'index' column. For now, I am creating a new collection to do this. The new collection is Col_Table1_Indexed. The desired table would therefore look like this:

 

Building Element Name:              Project ID                                                     Index

Superstructure                              Building Refurbishment in Michigan           1

Superstructure                              New Build in Kansas                                    2

Superstructure                              Extension in Arizona                                    3

 

 

I tried using the following formula, based on the 'last(firstN) principle. 

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
Collect(Col_Table1_Indexed, 
Last(FirstN(AddColumns(Col_Table1,"Index",CountRows(Col_Table1_Indexed)+1))))) 

 

Unfortunately this just duplicates the first collection row 3 times, so the 2nd and 3rd rows are ignored:

Building Element Name:              Project ID                                                     Index

Superstructure                              Building Refurbishment in Michigan           1

Superstructure                              Building Refurbishment in Michigan           2

Superstructure                              Building Refurbishment in Michigan           3

 

 

I am therefore trying to accomplish 2 things:

 

1) Add the subsequent rows (rows 2 and 3 in this case) without problems. 

2) Ideally, I would like to add the index column to the existing collection, Col_Table1, via an addColumns() approach. I attempted this earlier but with no success. 

 

Any ideas? Thanks. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@Anonymous 

There is not a real clean way to add a simple index to a collection, but it can be done with a secondary collection fairly easily.

Consider using this formula in your app:

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
   Collect(Col_Table1_Indexed,
      Patch(Last(FirstN(Col_Table1, CountRows(Col_Table1_Indexed)+1)), 
            {Index: CountRows(Col_Table1_Indexed) + 1})
          )
   )

In this case we need to add the column with a patch statement as the AddColumns statement would be for an entire table/collection not a single item.

So, in this formula, we're adding a patched record from the original collection based on its location (determined by the size of the new collection) and then using that count as another column in the record called "Index".

That should give you what you are looking for.

 

I hope it is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

4 REPLIES 4
RandyHayes
Super User III
Super User III

@Anonymous 

There is not a real clean way to add a simple index to a collection, but it can be done with a secondary collection fairly easily.

Consider using this formula in your app:

Clear(Col_Table1_Indexed);

ForAll(Col_Table1,
   Collect(Col_Table1_Indexed,
      Patch(Last(FirstN(Col_Table1, CountRows(Col_Table1_Indexed)+1)), 
            {Index: CountRows(Col_Table1_Indexed) + 1})
          )
   )

In this case we need to add the column with a patch statement as the AddColumns statement would be for an entire table/collection not a single item.

So, in this formula, we're adding a patched record from the original collection based on its location (determined by the size of the new collection) and then using that count as another column in the record called "Index".

That should give you what you are looking for.

 

I hope it is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Anonymous
Not applicable

This is exactly what I was looking for. Many thanks. Apologies for the delay in replying due to an early weekend! 🙂

 

Yet again you solve my Collection woes.. cheers!

OscarSotoCL
Frequent Visitor

Excelent Solution, Thank You  so much!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,893)