The IN Operator in M / Power Query

Are you trying to use the IN operator in Power Query but don’t know how? While the IN operator isn’t built into Power Query, you can still achieve the same functionality using other methods. This article will show you how to use the IN operator in Power Query.

The Need for the IN Operator

When working with Power Query, especially when creating conditional if statements, you often need to check if a value matches one of many possible options. Typically, this is done using multiple OR operators.

if [Month] = "January" or [Month] = "February" or [Month] = "March" 
   then "Q1"
   else "Remainder"

While this approach works, it can become cumbersome and hard to maintain as the number of options increases. Imagine if you had to check for 11 or more months. The code would be lengthy and prone to errors.

In many programming languages, the IN operator simplifies this process. The IN operator allows you to check if a value exists within a list, essentially serving as a shorthand for multiple OR statements. This can be especially helpful when you want to categorize data based on a predefined set of values. Unfortunately, Power Query doesn’t have a built-in IN operator, but we can replicate its functionality using other methods.

Example Scenario: Categorizing Products in Power Query

Let’s consider a practical example to understand the need for the IN operator better. Suppose you own a company that sells clothes and you currently offer a discount on Jeans, Skirts, and Tights. You want to categorize these products as “Sale” items and all other products as “Regular” items.

Without the IN operator, you would write the conditional statement like this:

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

This statement is straightforward, but it becomes increasingly complex and difficult to manage as the list of products grows. Each new product you want to include in the “Sale” category requires an additional OR condition, making the code lengthy and repetitive.

To make your code more concise and maintainable, you can use the List.Contains function in Power Query to replicate the IN operator. Here’s how you can achieve the same result without the repetitive OR statements:

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

Benefits of Using List.Contains

Using List.Contains instead of multiple OR operators offers several advantages:

• Conciseness: Your code is shorter and easier to read.
• Maintenance: Updating the list of products is straightforward. You only need to change the values within the List.Contains function.
• Clarity: The purpose of your code is clearer. It explicitly shows that you are checking if a value exists within a predefined list.

Combining Multiple Conditions with List.Contains

You can also combine conditions to check for multiple OR conditions at once. For instance, if you want to categorize products that are on sale only if they match both a specific product type and color, you can use a construct like this:

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

In this example:

• List.Contains( { "Jeans", "Skirts", "Tights" }, [Product] ) checks if the product is one of the specified types.
• List.Contains( { "Blue", "Red", "Yellow" }, [Color] ) checks if the color is one of the specified colors.
• The combined condition and ensures that both criteria must be met for the product to be categorized as “Sale”.

Excluding Specific Items with the Not Operator

To mark all products except the ones in your list as “Sale,” you can reverse the condition using the not operator. This is useful when you want to exclude certain products from the sale.

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

Here’s how it works:

• List.Contains( { "Jeans", "Skirts", "Tights" }, [Product] ) checks if the product is in the specified list.
• not inverts the check, so the statement categorizes as “Sale” any product not in the list.

The IN Operator with Multiple Columns

In Power Query, you can replicate the IN operator for multiple columns. This is useful when you need to check if a combination of values exists in a table.

Using List.Contains for Multiple Columns

To check for combinations of values using lists, you can apply the following approach:

List.Contains(
  { {"Jeans", "Blue"},
    {"Skirt", "Yellow"} },
    { [Product], [Color] }  // Turns Columns into list
)

In this code:

• The second argument contains a list with the two required column values.
• This list is compared with a list of inner lists specified in List.Contains‘ first argument.
• This allows you to compare multiple columns at once, checking if the combination of [Product] and [Color] matches any of the combinations in the list.

Using Table.Contains for Multiple Columns

You can also achieve this using tables. In that case you would use the Table.Contains function as follows:

Table.Contains(
    #table(                       // creates table
       {"Product", "Color"},      // with column names
       { {"Jeans", "Blue"},       // values 1st row
         {"Skirt", "Yellow"} } ), // values 2nd row
    [[Product], [Color]]          // checks if values exist in table
  )

In this approach:

• The #table function creates a table with specified column names and values.
• Table.Contains then checks if the combination of [Product] and [Color] exists in the table.

Using Table.FromRecords for Multiple Columns

This is very similar to using the below construct that stores the values within a record and transforms them into a table using Table.FromRecords:

Table.Contains(
     Table.FromRecords(
         { [Product = "Jeans", Color = "Blue"],
           [Product = "Skirt", Color = "Yellow"] },
         type table [Product = Text.Type, Color = Text.Type]
     ),
  [[Product], [Color]]
 )

Here’s how it works:

• Table.FromRecords creates a table from a list of records.
• Each record represents a combination of values for [Product] and [Color].
• Table.Contains checks if the combination of [Product] and [Color] exists in this table.

These were but three examples. There are several other ways to construct tables that result in similar outcomes. If this intrigued you, makes sure to read the deep dive on constructing table values in Power Query.

Conclusion

By understanding how to replicate the IN operator in Power Query, you can significantly improve your code. Whether you’re working with single columns, multiple columns, or complex combinations of conditions, using functions like List.Contains and Table.Contains can make your code more efficient, readable, and maintainable.

This not only helps you handle more complex conditions but also makes sure your Power Query scripts are clean and easy to manage.

Happy querying!