List.ContainsAny

Updated on

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

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

Syntax

List.ContainsAny(
   list as list,
   values as list,
   optional equationCriteria as any,
) as logical
ArgumentAttributeDescription
listThe list of values to check.
valuesThe values to check existence of within 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.ContainsAny function can be used to check whether a list of values contains one or more values from another list. If the value is found, this function return true. In all othercases it returns false. You can specify how the function should compare values by providing an equationCriteria in the third argument.

Examples

To understand List.ContainsAny, let’s look at a few examples.

Suppose we have the list of letters { "a", "b", "c" }. We want to test if any of the values from another list { "b", "z" } are included in the first list. We can do that as follows:

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

In this case, the comparison finds a match for the value "b", which is available in both lists. When none of the values match, this function will return false, as illustrated below:

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

Applying an equationCriteria

When the way you compare values makes a difference, the equationCriteria becomes important. It allows you to specify how to compare values by using a comparer function. For instance, have a look at the following expressions:

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

By default, the List.ContainsAny function compares values using ordinal comparison rules (using Comparer.Ordinal under the hood). For that reason, both of these expressions are identical and return false.

With a slight adjustment, adding the Comparer.OrdinalIgnoreCase enumeration, we can ignore capitalization when comparing values. The following example therefore returns true.

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

In scenarios where local spelling conventions make a difference, we could also respect particular culture codes. For instance, the following expressions use a culture code using Comparer.FromCulture:

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

// Output: true
List.ContainsAny( {"Færdig" }, { "Faerdig", "b" }, 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.ContainsAny in the following articles:

Other functions related to List.ContainsAny are:

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