List.ContainsAll Function in Power Query

The List.ContainsAll function in Power Query returns true if all items in a list are found in another list, false otherwise.

Syntax

List.ContainsAll(
    list as list, 
    values as list, 
    optional equationCriteria as any
) as logical 

The function has the following parameters:

• list: The main list to check within.
• values: The list of values we want to check are all present in the first list.
• equationCriteria: It is an optional parameter. It specifies how to compare values. Common options:
  • Comparer.Ordinal → Case-sensitive
  • Comparer.OrdinalIgnoreCase → Case-insensitive

Example: Basic check.

Power Query M

let
    Source = List.ContainsAll({"Ashish", 1, 5}, {"Ashish", 1})
in
    Source    

The output the above code is true.

Example: Case-Insensitive Check (Strings)

Power Query M

let
    // Main list
    mainList = {"Apple", "Banana", "Cherry"},

    // Values to check
    valuesToCheck = {"banana", "cherry"},

    // Check if all values exist in the main list (case-insensitive)
    result = List.ContainsAll(mainList, valuesToCheck, Comparer.OrdinalIgnoreCase)
in
    result     

The output the above code is true. Matches even though cases are different.

Example: Not containing all.

Power Query M

let
    // Main list
    mainList = {1, 2, 3},

    // Values to check
    valuesToCheck = {2, 4},

    // Check if all values exist in the main list
    result = List.ContainsAll(mainList, valuesToCheck)
in
    result         

The output of the above code is false because the value 4 is not present in the list {1, 2, 3}.