Table.PositionOfAny

Updated on

Table.PositionOfAny is a Power Query M function that locates the row position(s) of the first occurrence of a list of rows in a table. The function returns the row position(s) or -1 if no occurrences are found.

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

Syntax

Table.PositionOfAny(
   table as table,
   rows as list,
   optional occurrence as nullable number,
   optional equationCriteria as any,
) as any
ArgumentAttributeDescription
table
rows
occurrenceoptionalThe 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).
equationCriteriaoptionalUses 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.PositionOfAny finds the positions of the first occurrences of rows in the list rows within the table. Returns -1 if no occurrence is found. Optional parameters include occurrence and equation criteria for row comparison.

Examples

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

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

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

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

Other functions related to Table.PositionOfAny are:

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