Power BI – DAX List.Select syntax to return specific records

I unfortunately discovered today that I cannot use SQL to return a subset of records when Power BI references a SQL Server DB as a data source. I’m trying to do something similar to a SQL where clause using “IN”

for example

SELECT (*) FROM Table1
WHERE column_name IN ('a','b','f')

I’ve tried this (and several other variations) in DAX but get an error for Token Literal Expected:

Table.SelectRows(Table1, each (List.Select({'a','b','f'}) as [column_name])

Is there a way to accomplish this in DAX?

Answer

In DAX, you’d write it like this:

FILTER ( Table1, Table1[column_name] IN { "a", "b", "f" } )

However, it appears you are actually asking about the M language (used in the Query Editor).

In M, you can write it something like this:

Table.SelectRows(Table1, each List.Contains({"a", "b", "f"}, [column_name]))