Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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.


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.

The IN Operator in Power Query

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"
Elaborate conditional if statement in Power Query

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 operator in Power Query

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"
Multiple In Operators in Power Query

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(
         { [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.


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!

Share on:

Latest from my blog

  1. Thanks a lot, this was explained very clearly and I managed to do what I was trying to achieve for the last few hours in just a few minutes!

  2. Hi Rick,
    1-During the introduction in your statement you mentioned that the List.Contains function “…[Product] column equals one of the values in the list” (emphasizing the equals). shouldn’t that be “…contains…” or “…is in…”?
    2-how does actually the List.Contains work: it checks whether the 1st argument is in the 2nd argument or checks whether the 2nd argument is in the 1st argument? because as I read on ms.com documentation, it reads the oppposite of what you explained in the video. by the way, does the order matter?

    • Erivaldo – Thanks for asking, your questions are very relevant. Let’s delve in.

      1. The List.Contains function takes a list as first argument. In the example of the post I wrote:

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

      The formula refers to the column called [Product]. This formula is executed for each line in Power Query. That means that the Product column (2nd argument) in my example contains a single value for each execution.

      Each line the formula checks whether the value in the product column is available in the manual list provided in the 1st argument. The List.Contains function performs the comparison and checks whether the value is equal.

      That means:

      = List.Contains( { "Jeans", "Skirts", "Tights" }, "Jeans" ) // Returns true
      = List.Contains( { "Jeans", "Skirts", "Tights" }, "Jea" )   // Returns false 

      So by default the function does not check for a substring, but the term ‘Contains’ within the function name can confuse here.

      2. So how does List.Contains work? It checks whether the value in argument 2 is available in the list of argument. That’s also in line with what Microsoft writes :
      “… Indicates whether the list contains the value. “

    • Agba – this function folds as many times as you like. I have written extensive case-when statements using the function at least 15 times in one statement.

      I did notice that folding breaks once you have the List.Contains function check for null values, next to for example text values. So keep an eye out for that one.

      = List.Contains( {"A", "B" },       [Column1] ) // Formula folds
      = List.Contains( {"A", "B", null }, [Column1] ) // Does not fold
      // Formula folds
      = List.Contains( {"A", "B" }, [Column1] ) or [Column1] = null

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.