Data - How to get a row by ordinal number

Following an update to Power FX, we can now retrieve a record by row number/ordinal number by calling the Index function. The official documentation for this new feature is here:

The introduction of this feature is to support upcoming JSON parsing functionality. In the meantime, the introduction of the new Index function will simplify scenarios where we want to retrieve a row by ordinal number.

How to retrieve an item by row number

The basic use of the Index function looks like this.

Index(datasource, index)

The first argument defines the input data, which could be a collection, table data, or an online/live data source.

The second argument defines the index or row number. This is value one based - (eg, 1 will return the first record).

What's the use case for retrieving records by ordinal number?

There are many practical use cases for needing to return a record based on a specific row number.

As an example, there could be a requirement to build a reporting/dashboard type screen that shows data values based on row number.

The ability to retrieve records by the number is also useful in transformation scenarios such as those that are needed to transpose rows to columns, particularly when saving to non-normalised data structures.

The built-in budget tracker sample app includes formulas that retrieve data from collections based on the ordinal number. The screenshot beneath shows how prior to the Index function, we needed to write convoluted formula that utilised the Last/FirstN functions to carry out this task.

According to the documentation, an error occurs when we attempt to call Index to return a row that doesn't exist.

For practical purposes, if we want to detect that Index fails to return a row, we can do so by calling the IsBlankOrError function. The screenshot below highlights how this returns true when we attempt to return a row from a collection that doesn't exist (row 16).

When we attempt to call Index against a live online data source, an important point to note is that the function is not delegable. Let's take the example of a large SharePoint list called Clients.

If we attempt to return row 3,000 (which falls outside of the bounds of the maximum 'data row limit' setting), Power Apps will fail to return the required record.

Index(Clients, 3000)

We can verify this behaviour through the Monitor utility, as shown below. Therefore, Index will only rows up to the value in the 'data row limit' setting for online data sources.