First, FirstN, Index, Last, and LastN functions

Applies to: Canvas apps Desktop flows Model-driven apps Power Platform CLI

Returns the first, last, or a specific record, or a set of first or last records, from a table.

Description

The First function returns the first record of a table.

The FirstN function returns the first set of records of a table; the second argument specifies the number of records to return.

The Last function returns the last record of a table.

The LastN function returns the last set of records of a table; the second argument specifies the number of records to return.

The Index function returns a record of a table based on its ordered position in the table. Record numbering begins with 1 so First( table ) returning the same record as Index( table, 1 ). Index returns an error if the requested record index is less than 1, greater than the number of records in the table, or the table is empty.

First, Index, and Last return a single record. FirstN and LastN return a table, even if you specify only a single record.

Delegation

When used with a data source, these functions can't be delegated. Only the first portion of the data source will be retrieved and then the function applied. The result may not represent the complete story. A warning may appear at authoring time to remind you of this limitation and to suggest switching to delegable alternatives where possible. For more information, see the delegation overview.

For example, when used with a data source containing a large table with 1 million records, Last will be subject to the non-delegation limit and will not return the last record of the entire data source. Likewise, using Index to request a record in the middle of 1 million records will result in an error because the index is out of range based on the non-delegation limit.

Syntax

First( Table )
Last( Table )

  • Table - Required. Table to operate on.

FirstN( Table [, NumberOfRecords ] )
LastN( Table [, NumberOfRecords ] )

  • Table - Required. Table to operate on.
  • NumberOfRecords - Optional. Number of records to return. If you don't specify this argument, the function returns one record.

Index( Table, RecordIndex )

  • Table - Required. Table to operate on.
  • RecordIndex - Required. The index of the record to return. Record numbering begins with 1.

Examples

For the following examples, we'll use the IceCream data source, which contains the data in this table:

IceCream example.

This table can be placed in a collection with this formula (put in the OnStart formula for a Button control and press the button):

Collect( IceCream, Table( { Flavor: "Chocolate", Quantity: 100 },
                          { Flavor: "Vanilla", Quantity: 200 },
                          { Flavor: "Strawberry", Quantity: 300 },
                          { Flavor: "Mint Chocolate", Quantity: 60 },
                          { Flavor: "Pistachio", Quantity: 200 } ) )
Formula Description Result
First( IceCream ) Returns the first record of IceCream. { Flavor: "Chocolate", Quantity: 100 }
Last( IceCream ) Returns the last record of IceCream. { Flavor: "Pistachio", Quantity: 200 }
Index( IceCream, 3 ) Returns the third record of IceCream. { Flavor: "Strawberry", Quantity: 300 }
FirstN( IceCream, 2 ) Returns a table containing the first two records of IceCream. Table containing the records for Chocolate and Vanilla
LastN( IceCream, 2 ) Returns a table containt the last two records of IceCream. Table containing the records for Mint Chocolate and Pistachio
Index( IceCream, 4 ).Quantity Returns the fourth record of the table, and extracts the Quanity column. 60
Index( IceCream, 10 ) Returns an error since the record requested is beyond the bounds of the table. Error