The IN Operator in M / Power Query

Are you trying to use the IN operator in Power Query but don’t know how? The IN operator isn’t built into Power Query, but we can still achieve the same thing using other functions. In this article, I’ll show you how to use the IN operator in Power Query.

Introduction

When creating conditional if statements, a common requirement is to check if a value equals one of many. In this statement, you could combine multiple OR operators.

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.

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 type the list of products manually, it also involves a lot of unnecessary repetition of code. In this case, the column [Product] repeats for each product.

Replicating the IN operator

So, what code can you use to replicate the IN operator? 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 use the Table.Contains function and involves working with tables and records and can look like this:

= 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 about constructing tables in Power Query.

Conclusion

This article showed how you can replicate the IN operator in the Power Query M language. The IN operator may not be built into Power Query yet, but with other functions, we can achieve the same results.

It makes your code much shorter when checking if a value is in a list. This is helpful when checking one column, but it can also work with multiple columns. Now, you can go ahead and use these functions in your own projects.

I hope you found this article helpful and that you’re now more comfortable with using the IN operator in Power Query. If you have any questions or other methods you use, feel free to share them in the comments below.

Happy querying!