Power Query's Missing IN Operator: No Problem! List.Contain is Here!

The IN operator is a common feature in programming languages, allowing users to check if a value exists within a specified list. While Power Query M doesn't have a built-in IN operator, we can still achieve the same functionality using alternative methods. This article will guide you through replicating the IN operator in Power Query M using various techniques.

Understanding the IN Operator

The IN operator simplifies conditional statements by enabling users to compare a value against a list of values simultaneously. This eliminates the need for multiple OR operators, making the code more concise and efficient. Here's how you would use the IN operator in SQL:

--Using OR Operator
SELECT
    CASE 
        WHEN Country = 'Canada' OR Country = 'United States' THEN 'North America' 
        WHEN Country ='Australia' THEN 'Australia' 
        WHEN Country ='France' OR Country = 'Italy' OR Country ='Germany' OR Country ='Netherlands' OR Country ='United Kingdom' THEN 'Europe & UK' 
        ELSE 'Online' 
    END [Continent], *
FROM Store

-- Using IN Operator
SELECT
    CASE 
        WHEN Country IN ('Canada','United States') THEN 'North America' 
        WHEN Country ='Australia' THEN 'Australia' 
        WHEN Country IN('France', 'Italy','Germany','Netherlands','United Kingdom') THEN 'Europe & UK' 
        ELSE 'Online' 
    END [Continent], *
FROM Store

However, when creating a continent column in Power Query M code, you may encounter the error message "Token then expected". This error occurs when we try to use the IN operator in M code, which is not the correct syntax.

Replicating the IN Operator with List.Contains

The List.Contains function serves as a substitute for the IN operator in Power Query M. It checks whether a value in a column matches any of the values in a provided list and returns True or False. Here's a syntax:

List.Contains(list as list, 
              value as any, 
              optional equationCriteria as any) as logical

List.Contains({1,2,3,4,5},2) //Returns True
List.Contains({1,2,3,4,5},7) //Returns False

Now, Let's fix our M code that has a syntax error in it by using the List.Contains function.

if 
    List.Contains({ "Canada", "United States"},[Country]) 
        then "North America" else 
if 
    [Country] ="Australia" then "Australia" else 
if 
    List.Contains({"France", "Italy","Germany","Netherlands","United Kingdom"},[Country]) 
        then "Europe & UK" 
else "Online"

When utilizing the List.Contains function, we provide a list of criteria as the first argument. This list is then compared against the corresponding values in the specified column.

Excluding Values with NOT

To exclude values from the comparison, you can use the NOT operator in conjunction with List.Contains.

if not List.Contains({"France", "Italy","Germany","Netherlands"},[Country]) 
   then "Non-European Countries" 
else "European Countries"

List.Contains with Multiple Columns

The List.Contains can also be used for multiple columns. This is useful when checking for combinations of values across multiple columns.

if 
    List.Contains(
                    {
                        {"Canada","New Brunswick"},
                        {"United States","Washington DC"}
                    },
                    {[Country],[State]}) 
    then "Eastern Territories" 
else "Western Territories"

This code snippet checks if the combination of country and state exists in the provided list of pairs.

Alternative Approaches

There are several alternative approaches to replicating the IN operator in Power Query M, often involving constructing tables from lists such as Table.Contains. These methods offer flexibility in handling various data structures.

Conclusion

While the IN operator is not directly available in Power Query M, various techniques, such as List.Contains and Table.Contains, can effectively replicate its functionality. These methods enable users to check for values within lists and combine multiple conditions, making their code more concise and efficient for data filtering and categorization.

Power Query's Missing IN Operator

Table of contents