List.PositionOfAny is a Power Query M function that returns the offset in a list of the first occurrence of a value in another list. The function returns the offset or -1 if no occurrence is found, with an optional occurrence parameter.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
List.PositionOfAny(
list as list,
values as list,
optional occurrence as nullable number,
optional equationCriteria as any,
) as anyDescription
The List.PositionOfAny function is used to find the positions of specific elements within a list. It takes as inputs the list to search, a list of values to find, and optionally, the maximum number of matches to return and a comparison criteria.
By default, the function returns the position of the first occurrence of any of the specified values. However, with the Occurrence parameter, it can be adjusted to return the last occurrence or all occurrences. The function can also be made case-insensitive by using Comparer.OrdinalIgnoreCase, allowing for more flexibility in your matching criteria.
Examples
Finding Single or Multiple Positions
To determine the position of the first occurrence of either 2 or 3 in the list { 1, 2, 3, 4 }, the function is used as follows:
// Returns 1
List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 } )The function quickly locates elements within the list and returns its index position. By default the function always returns the position of the first match it finds. You can however change this using the Occurrence Parameter.
Using the Occurrence Parameter
The previous example is identical to the next one that uses Occurrence.First:
// Returns 1
List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.First )You can adjust this expression to make use of Occurrence.All to return multiple positions:
// Returns { 1, 2 }
List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.All )For the last occurrence, you can use Occurrence.Last as follows:
// Returns 2
List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.Last )
These variations show how you can adjust your parameters to return either the first, last, or all matching positions based on your desired criteria.
Case Sensitivity in Searches
By default, List.PositionOfAny is case-sensitive:
// Returns {}, as there are no exact case matches
List.PositionOfAny(
{ "ape", "duck", "ape", "cow" },
{ "APE", "DucK" },
Occurrence.All
)
This is identical to using:
// Returns {}, as there are no exact case matches
List.PositionOfAny(
{ "ape", "duck", "ape", "cow" },
{ "APE", "DucK" },
Occurrence.All,
Comparer.Ordinal
)Ignoring Case with Comparer.OrdinalIgnoreCase
For case-insensitive searches, use Comparer.OrdinalIgnoreCase:
// Returns { 0, 1, 2 }
List.PositionOfAny(
{ "ape", "duck", "ape", "cow" },
{ "APE", "DucK" },
Occurrence.All,
Comparer.OrdinalIgnoreCase
)Related articles
Learn more about List.PositionOfAny in the following articles:
- Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more
Related functions
Other functions related to List.PositionOfAny are:
