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
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
)
Related functions
Other functions related to Table.PositionOfAny are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy