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| Argument | Attribute | Description |
|---|---|---|
| table | ||
| rows | ||
| 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
Returns the row(s) position(s) from the table of the first occurrence of the list of rows. Returns -1 if no occurrence is found.
table: The input table.rows: The list of rows in the table to find the positions of.occurrence: [Optional] Specifies which occurrences of the row to return.equationCriteria: [Optional] Controls the comparison between the table rows.
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
)Related functions
Other functions related to Table.PositionOfAny are:
