Adding an index column to a collection

[Anonymous]

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. 

[RandyHayes · Accepted solution]

@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.

[Anonymous]

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

[JMAFO]

Yet again you solve my Collection woes.. cheers!

[OscarSotoCL]

Excelent Solution, Thank You  so much!

[Anonymous]

I think there is a cleaner way to create a primary key in a collection that doesn't involve creating a new collection and doesn't rely on a SharePoint list.  Say there is a collection named colTable and it already exists, and there is a column in that collection called RowNumber that you want to use as a primary key.  You can use ForAll, Sequence, and Patch to place a primary key into the RowNumber (or fix the numbering in RowNumber to get a contiguous list of integer values if you have removed some of the rows in the collection and your RowNumber values now have gaps in them) by using the following formula:

ForAll(Sequence(CountRows(colTable)),Patch(colTable,Last(FirstN(colTable,Value)),{RowNumber:Value}))

Value is the output of the Sequence function and provides a set of number from 1 to number of rows in the collection.

[AndrewLansford]

Great solution! I can expand it a bit. If the collection doesn't exist (say you're pulling data from SharePoint and want the server to do the sorting) you can use the WITH function to hold your items temporarily for you.

With(
    {
        items: SortByColumns(
            Filter(
                lstMySharepointList,
                Status.Value = "Created"
            ),
            "OriginalShipDate",
            Ascending
        )
    },
    ForAll(
        Sequence(CountRows(items)),
        Patch(
            Last(
                FirstN(
                    items,
                    Value
                )
            ),
            {RowNumber: Value}
        )
    )
)

[Woong]

Now we have index function so Last(FirstN()) can be simplified.

ForAll(
    Sequence(CountRows(collection)),
    Patch(
        collection,
        Index(collection, Value),
        {Index: Value}
    )
)

But as mentioned here, the column needs be declaired previously

[TedBabcock]

Forgive my impertinence to MS; I love Power Apps and use it all the time. But this is something that should have been provided when the Index() function was created. They KNOW the index of each row in a table; that's how Index(table, 1) can find the right row. They simply WON'T TELL US what that index is. It should be as easy to find the index of a row (ThisItem.Index, or some such) as it is to find a row using the index.

[PieterBollen]

• Add a column to the collection and CountRows on all rows where the ID is equal or smaller then the current ID.

ClearCollect(<NewCollection>,
     AddColumns(<SP-List> As SP, "myIndex",

          CountRows(Filter(<SP-List>, ID <= SP.ID))))