List.Contains - List Function

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

Argument	Attribute	Description
List		Represents the list you want to inspect.
Value		Indicates the value to search for in 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

In the context of the List.Contains function, 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.

Basic 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.

Dealing with Case Sensitivity

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.

Replicating the IN operator

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, replicating the IN operator:

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

This use of List.Contains here 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.

Multiple Conditional Statements

For more complex scenarios, for instance where multiple conditions need to be checked simultaneously, List.Contains can be used together 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:

The IN Operator in M / Power Query
This article describes the IN operator in Power Query. It checks whether a value equals one of many and is meant to simplify your code. » Read more
IF Statements in Power Query (Incl Nested IF)
Master IF Statements in Power Query! Explore IF functions, syntax, conditional logic, error handling, and advanced techniques. » Read more
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

Other functions related to List.Contains are:

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