Power query M the IN operator

Question

What would be equivalent of SQL IN operator for Power BI. Just like in clause:

where [Column1] IN ('Value1', 'Value2', 'Value3')

I am looking for M solution (not DAX).

Answer 1

You can use the List.Contains function.

For example,

= Table.SelectRows(Table1, each List.Contains({ "Value1", "Value2", "Value3" }, [Column1]))

will filter Table1 to include only rows where [Column1]'s value is contained in the given list.

Answer 2

Alexis suggestion is the easiest one.

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

In case you need to match multiple values, you can also use a different syntax. Let's say you want to match both the Product and the color of the product. And this combination needs to be either Blue Jeans, or Yellow Skirt. You can then write:

= List.Contains(  
  { {"Jeans", "Blue"},
  {"Skirt", "Yellow"} },
  { [Product], [Color] }  )

You can find examples on this construct, together with easier examples right here.

https://gorilla.bi/power-query/in-operator/