The equivalent of NOT IN (SQL) in power query

[akhaliq7]

I am currently using a sql native query and wish to use power query instead. I have a select subquery that uses a not in function like the one below:

(
SELECT DISTINCT MAX(column1)
FROM Table1 tb
WHERE tb.id = 1
   AND tb.id3 = base_table.id3
   AND NVL(tb.id, 0) NOT IN (
       SELECT NVL(tb2.id2 1)
       FROM Table1 tb2
       WHERE tb2.id = 11
       )
GROUP BY tb.id
) AS Alias

In the above query I am needing to do not in how would I do that in power query.

I am guessing I need to create two queries for table1 tb and tabl1 tb2

then need to do a not in between them

[Anonymous · Accepted solution]

It's the equivalent to

= Table.SelectRows(TableOrPriorStepName, each not List.Contains(ListOfInValues, [ColumnToCheck]))

--Nate

[AlexisOlson]

@akhaliq7 Note that what @Anonymous gave is equivalent to a simpler SQL expression:

SELECT *
FROM TableOrPriorStepName
WHERE [ColumnToCheck] NOT IN ListOfInValues