Table.PositionOfAny

Table

Returns the first position of any row in the table that matches any record in the given list.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

Table.PositionOfAny(table as table, rows as list, optional occurrence as nullable number, optional equationCriteria as any) as any

Parameters

NameTypeRequiredDescription
tabletableYesThe table to search.
rowslistYesA list of records to search for.
occurrencenumberNoOptional. Use Occurrence.First (0), Occurrence.Last (1), or Occurrence.All (2) to control which positions are returned.
equationCriteriaanyNoOptional. Column name(s) or comparer to restrict which fields are compared.

Return Value

any — A row index, a list of row indices, or -1 if no match is found.

Remarks

Table.PositionOfAny is the multi-candidate variant of Table.PositionOf. Instead of searching for a single record, it accepts a list of candidate records and returns the position of the first row in the table that matches any of them. If no candidate matches any row, it returns -1.

The occurrence parameter controls the return behavior: - Occurrence.First (default): the index of the first row matching any candidate. - Occurrence.Last: the index of the last row matching any candidate. - Occurrence.All: a list of all row indices that match any candidate.

The optional equationCriteria parameter restricts which columns are compared, enabling partial record matching across all candidates simultaneously. This function is useful when you have a set of "sentinel" or "anchor" rows whose positions you need to locate, for example to determine slice boundaries in a structured flat file.

Examples

Example 1: Find the first position of any of several customers

let
    Sales = #table(
        {"OrderID", "CustomerName", "Product"},
        {{1,"Alice","Widget A"},{2,"Bob","Gadget B"},{3,"Charlie","Widget C"},{4,"Diana","Gadget D"}}
    ),
    Targets = {[CustomerName = "Charlie"], [CustomerName = "Diana"]}
in
    Table.PositionOfAny(Sales, Targets, Occurrence.First, "CustomerName")
Result
Result
12

Example 2: No candidate matches — returns -1

let
    Products = #table(
        {"ProductName"},
        {{"Widget A"},{"Gadget B"},{"Widget C"}}
    ),
    Searches = {[ProductName = "Thingamajig E"], [ProductName = "Widget Z"]}
in
    Table.PositionOfAny(Products, Searches, Occurrence.First, "ProductName")
Result
Result
1-1

Example 3: Return all positions matching any of the target customers

let
    Sales = #table(
        {"OrderID", "CustomerName"},
        {{1,"Alice"},{2,"Bob"},{3,"Alice"},{4,"Charlie"},{5,"Diana"},{6,"Bob"},{7,"Alice"}}
    ),
    Targets = {[CustomerName = "Alice"], [CustomerName = "Bob"]}
in
    Table.PositionOfAny(Sales, Targets, Occurrence.All, "CustomerName")
Result
Result
1{0, 1, 2, 5, 6}

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks