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
Argument | Attribute | Description |
---|---|---|
list | The list of values to check. | |
values | The values to check existence of within the list. | |
equationCriteria | optional | Uses 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.
Related articles
Learn more about List.ContainsAny in the following articles:
- Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more
Related functions
Other functions related to List.ContainsAny are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy