List.PositionOfAny Function in Power Query

The List.PositionOfAny function in Power Query (M language) returns the position of the by default first occurrence of any item from a list of values in a target list. If none are found, it returns -1.

Syntax

List.PositionOfAny(
    list as list, 
    values as list, 
    optional occurrence as nullable number, 
    optional equationCriteria as any
) as number  

The function has the following parameters:

• list: The list to search within.
• values: A list of values to search for.
• occurrence: It is an optional parameter. Specify which occurrence to return:
  • Occurrence.First: Returns the first occurrence (default).
  • Occurrence.Last: Returns the last occurrence.
  • Occurrence.All: Returns all matching positions.
• equationCriteria: It is an optional parameter. Defines how values are compared. Common comparers:
  • Comparer.Ordinal: Case-sensitive (default).
  • Comparer.OrdinalIgnoreCase: Case-insensitive for text.

Example: Find the index position of any item from the list {"Mango", "Orange"} in the given source list. The input value is case sensitive.

Power Query M

let
    SourceList = {"Apple", "Banana", "Mango", "Orange"},
    ValuesToFind = {"Mango", "Orange"},
    Position = List.PositionOfAny(SourceList, ValuesToFind)
in
    Position  

The output of the above code is 2.

Example: Not Found.

Power Query M

let
    SourceList = {"Apple", "Banana", "Mango", "Orange"},
    ValuesToFind = {"mango", "orange"},
    Position = List.PositionOfAny(SourceList, ValuesToFind)
in
    Position 

The output of the above code is -1, because none of the values are found. By default, it is case-sensitive.

Example: Case-Insensitive Match.

Power Query M

let
    SourceList = {"Apple", "Banana", "Mango", "Orange"},
    ValuesToFind = {"mango", "orange"},
    Position = List.PositionOfAny(SourceList, ValuesToFind, 0, Comparer.OrdinalIgnoreCase)
in
    Position 

The output of the above code is 2.