List.PositionOfAny

Updated on

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 any
ArgumentAttributeDescription
list
values
occurrenceoptionalThe 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).
equationCriteriaoptionalUses 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

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 
)

Learn more about List.PositionOfAny in the following articles:

Other functions related to List.PositionOfAny are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-positionofany