cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MaxDonen
Level: Powered On

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
Highlighted
Super User
Super User

Re: Adding an index column to a collection

@MaxDonen 

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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Adding an index column to a collection

@MaxDonen 

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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

MaxDonen
Level: Powered On

Re: Adding an index column to a collection

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

 

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,500)