List.ContainsAll

Updated on
Rick de Groot

List.ContainsAll is a Power Query M function that checks if a given list includes all the values from another list, with optional equation criteria for equality testing. The function returns true if all values are found in the list and false otherwise.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.ContainsAll(
   list as list,
   values as list,
   optional equationCriteria as any,
) as logical
ArgumentAttributeDescription
listA list of values to check.
valuesThe values to check with the list.
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.ContainsAll functions allows you to check whether a provided list of values contains all values from another list. When all values of the second list can be found within the first list, the function returns true. In all other cases it returns false. You can optionally specify an equationCriteria to control case sensitivity and cultural nuances when comparing values.

Examples

Let’s look at some examples of how you can use the List.ContainAll function.

Basic Examples

Suppose you have a list with the values { "a", "b", "c" }. You want to check whether the input list contains the values “a” and “c”.

List.ContainsAll(  { "a", "b", "c" },  { "b", "c" } ) // Output: true

You can perform the same type of comparison with a mixed list of values: 

// Output: true
List.ContainsAll(  
  { "a", 1, #date( 2023, 1, 1 ), true }, 
  {  1, true } 
)

However, in case a value is missing, the function will return false as a result: 

List.ContainsAll(  { "a", "b", "c" },  { "b", "z" } ) // Output: false

Applying an equationCriteria

Also note that when you try to compare values, but some are capitalized and others aren’t, they are considered unequal with the standard comparison rules. By default, the List.ContainsAll function makes use of the Comparer.Ordinal enumeration. Therefore, the following statements are identical: 

// Output: false
List.ContainsAll(  { "a", "b", "c" },  { "A", "B" } ) 
List.ContainsAll(  { "a", "b", "c" },  { "A", "B" }, Comparer.Ordinal )

If you want the comparison to ignore capitalization, you can make use of the Comparer.OrdinalIgnoreCase function as follows: 

// Output: true
List.ContainsAll(  { "a", "b", "c" },  { "A", "B" }, Comparer.OrdinalIgnoreCase )

There may even be scenarios, where depending on the local spelling conventions used a value may be equal, or be may different. For instance, the following examples use two variations that specify a culture code using Comparer.FromCulture: 

// Output: false
List.ContainsAll( {"Færdig" }, { "Faerdig" } )
List.ContainsAll( {"Færdig" }, { "Faerdig" }, Comparer.FromCulture(  "da-DK") )

// Output: true
List.ContainsAll( {"Færdig" }, { "Faerdig" }, Comparer.FromCulture(  "en-US") )

Depending on whether we respect the danish da-DK culture, or the english en-US culture, the result will be false or true respectively.

Learn more about List.ContainsAll in the following articles:

Other functions related to List.ContainsAll are:

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

2023-2026 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy