Table.PositionOf
TableReturns the zero-based row index of the first (or all) rows matching the given record, or -1 if not found.
Syntax
Table.PositionOf(table as table, row as record, optional occurrence as nullable number, optional equationCriteria as any) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to search. |
row | record | Yes | A record whose fields are matched against table rows. |
occurrence | number | No | Optional. Use Occurrence.First (0), Occurrence.Last (1), or Occurrence.All (2) to control which positions are returned. |
equationCriteria | any | No | Optional. Column name(s) or comparer to restrict which fields are compared. |
Return Value
any — A row index number, a list of row indices, or -1 if no match is found.
Remarks
Table.PositionOf returns the zero-based row index of a row matching the given record within the table. If no match is found, it returns -1. The optional occurrence parameter controls how multiple matches are handled:
Occurrence.First(default): returns the index of the first match.Occurrence.Last: returns the index of the last match.Occurrence.All: returns a list of all matching row indices.
The optional equationCriteria parameter restricts which columns participate in the comparison — useful when you only care about matching on a subset of fields.
Table.PositionOf is useful when you need to locate rows by content in order to perform positional operations such as Table.RemoveRows, Table.InsertRows, or Table.ReplaceRows. For simply checking whether a row exists (without needing its position), Table.Contains is more concise. This function does not fold to data sources.
Examples
Example 1: Find the position of a specific order
let
Sales = #table(
{"OrderID", "CustomerName", "Product"},
{{1,"Alice","Widget A"},{2,"Bob","Gadget B"},{3,"Charlie","Widget C"},{4,"Diana","Gadget D"}}
)
in
Table.PositionOf(Sales, [OrderID = 3, CustomerName = "Charlie", Product = "Widget C"])The final output — Table.PositionOf finds the row [OrderID = 3, CustomerName = "Charlie", Product = "Widget C"] at zero-based position 2 (the third row).
Result | |
|---|---|
| 1 | 2 |
Example 2: Row not found returns -1
let
Products = #table(
{"ProductID", "ProductName"},
{{1,"Widget A"},{2,"Gadget B"},{3,"Widget C"}}
)
in
Table.PositionOf(Products, [ProductID = 99, ProductName = "Unknown"])The final output — Table.PositionOf returns -1 because no row matches [ProductID = 99, ProductName = "Unknown"].
Result | |
|---|---|
| 1 | -1 |
Example 3: Find all positions where a customer appears
let
Sales = #table(
{"OrderID", "CustomerName"},
{{1,"Alice"},{2,"Bob"},{3,"Alice"},{4,"Charlie"},{5,"Alice"}}
)
in
Table.PositionOf(Sales, [CustomerName = "Alice"], Occurrence.All, "CustomerName")The final output — with Occurrence.All and the CustomerName column as the comparison key, Table.PositionOf returns every matching position: {0, 2, 4}.
Result | |
|---|---|
| 1 | {0, 2, 4} |
Compatibility
