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.

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.

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

Happy querying!

Share on:
  1. I’m getting an error that says I’m passing 4 arguments for a function that expects 2 or 3, and that kind of defeats the purpose of having this function. Has anyone else had this happen?

    Reply
  2. Hi Rick,
    brilliant explanation. Thank you. I have a question that puzzles me. If I try to make a list from a selection of values in a column and I call this in a prior step, say, ClothesList, when I then insert this list into the code below like this:

    if List.Contains( ClothesList, [Product] ) 
       then "Sale"
       else "Regular"

    I get an error to the tune that it cannot find a list argument in a list. The reasons for this approach is that I would like to use a List.Select type of step to make my list dynamic and not hard coded. Any light you may shed on this point, is very useful. Thank you again for putting together this treasure trove of M wisdom

    Reply
  3. 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!

    Reply
  4. 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?

    Reply
    • 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. “

      Reply
    • 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
      Reply

Leave a comment

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