The IN Operator in M / Power Query

When creating conditional if statements, a common requirement is to check if a value equals one of many. In most programming languages you can find the IN operator to achieve this. The IN operator is then meant to simplify conditions so one can easily provide a list of values to check against. At the moment of writing, the M language does have the IN operator. However, in this post I will show you a way that achieves the same with a different approach.

Let’s say you have a company that sells clothes. You currently have a discount for Jeans, Skirts and Tights. You want to categorize them by “Sale” and “Regular”. You can categorize them by creating an if-statement:

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

This statement works well and returns the correct results in a verbose way. We illustrated this with just 3 products, but imagine doing this for 15 products or more. Besides the fact that it’s very inconvenient to manually type the list of products, it also involves a lot of unnecessary repetition of code. In this case, the column [Product] repeats for each product.

So how can you simplify this? You can rewrite the above statement to:

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

In this way, the List.Contains function replicates the IN operator in M. It checks whether the value in the [Product] column equals one of the values in the list.

You can also combine this construct and check for multiple or-conditions at once.

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

To mark all products except the ones in your list, you can reverse the result by using the word not:

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

The IN Operator with Multiple Columns

You can also replicate the IN operator on multiple columns. This can be useful if you want to check whether a combination of values exists in a table. The examples involve working with tables and records and can look like:

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

= 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
)

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

These were but three examples. There are several other ways to construct tables that result in similar outcomes. In the following article you can learn more on constructing tables in Power Query.

This article showed how you can replicate the IN operator in the Power Query M language. It shortens the code required to match a list of values. This is mostly useful when checking a condition on a single column but can also work on a combination of columns.

Recommend Reading

>>> Power Query - Foundations <<<List Functions (200+ examples)Text functions (150+ examples)Creating Tables from Scratch (40+ examples)Understanding If Statements 
Generating a Date Table


>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

1 thought on “The IN Operator in M / Power Query”

Leave a comment