List.Contains

Updated on

List.Contains is a Power Query M function that checks if a given list contains a specified value, with optional equation criteria for equality testing. The function returns true if the value is found in the list and false otherwise.

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

Syntax

List.Contains(
   list as list,
   value as any,
   optional equationCriteria as any,
) as logical
ArgumentAttributeDescription
ListRepresents the list you want to inspect.
ValueIndicates the value to search for in 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

In the context of this syntax, list represents the list that you want to inspect, value is the specific value that you’re looking for within the list, and the optional equationCriteria allows you to control the equality testing procedure. The function will then return a logical (“true” or “false”) output based on whether the value is found within the list.

Examples

To understand the application of List.Contains, let’s examine a few practical examples.

Suppose you are working with a numerical list, specifically integers ranging from 1 to 5. To identify if the integer 3 is present within the list, you can use List.Contains in the following manner:

List.Contains( {1, 2, 3, 4, 5}, 3 ) // Output: TRUE

The function returns “TRUE”, confirming the presence of the integer 3 in the list.

The List.Contains function can also be applied to heterogeneous lists, which contain a combination of different data types. For instance:

List.Contains( {"a", 2, "%", #date( 2023, 1, 1 ), 4, 5}, "%" ) // Output: TRUE

This result indicates the presence of the character “%” within the mixed data type list.

In the case of string comparisons, it is important to note that the default comparison is case-sensitive, using Comparer.Ordinal. For a case-insentive comparison, Comparison.OrdinalIgnoreCase should be used instead:

List.Contains( {"a", "b", "c", "d"}, "D" ) // Output: FALSE
List.Contains( {"a", "b", "c", "d"}, "D", Comparer.Ordinal ) // Output: FALSE

List.Contains( {"a", "b", "c", "d"}, "D", Comparer.OrdinalIgnoreCase ) // Output: TRUE

The last implementation of the function confirms that the letter “D” exists in the list, irrespective of case.

Another practical application of List.Contains is within a table. Consider a column named [Product], where it’s necessary to identify the presence of specific product names (such as “Jeans”, “Skirts”, or “Tights”). Traditionally, an extensive conditional statement could be written:

if [Product] = "Jeans" or [Product] = "Skirts" or [Product] = "Tights" 
   then "Sale"
   else "Regular"

However, with List.Contains, the code can be significantly simplified:

if List.Contains( { "Jeans", "Skirts", "Tights" }, [Product] ) 
   then "Sale"
   else "Regular"

This use of List.Contains reduces the complexity of the code while maintaining the accuracy of the product check. It also showcases the capability of the function to accept column references as list parameters.

For more complex scenarios where multiple conditions need to be checked concurrently, List.Contains can be used in conjunction with logical operators:

if List.Contains( { "Jeans", "Skirts", "Tights" }, [Product] )
      and List.Contains( { "Blue", "Red", "Yellow" }, [Color] )
   then "Sale"
   else "Regular"

Here, the function verifies the presence of specified conditions for both product and color, ensuring more precise conditions.

As these examples show, the List.Contains function provides a compact and efficient means of checking value presence in Power Query M allowing for cleaner code.

Learn more about List.Contains in the following articles:

Other functions related to List.Contains are:

BI Gorilla Youtube Channel

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