Forum Discussion
Power Query equivalent to SQL "WHERE EXISTS"
I'm trying to filter a table based on existence of a record in another table. If I was in SQL I'd use a "WHERE EXISTS".
It's possible to do it with a Table.Joins but in case the record is twice in the "other" table I'll add rows to my initial table. (Yes I could also do a distinct before the Table.Joins) ... but is there a more efficient way?
1 Reply
Cannot think of a more efficient way, especially if there are multiple different values in the "filter-column". There are List- (List.Intersect) and Table-operations (like Table.Contains or Table.PositionOf) who operate on row-level, but this means the full recordset, which probably isn't what you need here.
If you only want to filter, you don't have to expand the merged table but simply delete the joined table it after it did it's job. You need to use JoinKind.Inner.
If you're applying this to a SQL-database and your filter-table is longer than 200 rows, you might want to use this bug-workaround for query folding: http://www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-limitations-and-bug-workaround/
