Table.PositionOf - Table Function

Table.PositionOf is a Power Query M function that finds the row position of the first occurrence of a specified row in a table. The function returns the row position or -1 if the row is not found.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.PositionOf(
   table as table,
   row as record,
   optional occurrence as any,
   optional equationCriteria as any,
) as any

Argument	Attribute	Description
table
row
occurrence	optional	The Occurrence.Type specifies the occurrence of an element in a sequence. When omitted, Power Query defaults to returning the first match with Occurrence.First. Alternatively, you can return the last (Occurrence.Last) or all matches it finds (Occurrence.All).
equationCriteria	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

Table.PositionOf returns the position of the first occurrence of row in the table. Returns -1 if the row is not found. It includes optional parameters for occurrence and equation criteria to control row comparison.

Examples

Find the position of the first occurrence of [a = 2, b = 4] in the table ( {[a = 2, b = 4], [a = 6, b = 8], [a = 2, b = 4], [a = 1, b = 4]} ).

// Output: 0
Table.PositionOf( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 1, b = 4],
        [a = 2, b = 4],
        [a = 1, b = 4]
    } ),
    [a = 2, b = 4]
 )

Find the position of the second occurrence of [a = 2, b = 4] in the table ( {[a = 2, b = 4], [a = 6, b = 8], [a = 2, b = 4], [a = 1, b = 4]} ).

// Output: 2
Table.PositionOf( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 1, b = 4],
        [a = 2, b = 4],
        [a = 1, b = 4]
    } ),
    [a = 2, b = 4],
    1
 )

Find the position of all the occurrences of [a = 2, b = 4] in the table ( {[a = 2, b = 4], [a = 6, b = 8], [a = 2, b = 4], [a = 1, b = 4]} ).

// Output: {0, 2}
Table.PositionOf( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 1, b = 4],
        [a = 2, b = 4],
        [a = 1, b = 4]
    } ),
    [a = 2, b = 4],
    Occurrence.All
 )

Other functions related to Table.PositionOf are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-positionof

Syntax

Description

Examples

Related functions