Power Query Left Anti Merge: Extract the excluded item from the Data Table
Power Query Anti Left join is a neat tool for things like consolidations without using any formula.
The scenario is that, if you want to exclude from your report a bunch of rows and the keys you want to exclude are stored in a table.
You don't want to use a filter because the list of keys to exclude is dynamic and might change.
You may notice that there is a very elegant feature as part of merge queries in Power Query and these are the Left Anti and Right Anti types of merge. They seem like the perfect match for this scenario. In one step you can remove any rows in your main table that has a match in the other table.
So why not use it? The answer is that it is much faster to use a default outer join than the anti join types. The difference is big and can multiply the time to refresh by a factor of 3 or more.
After doing the outer join you expand the key from the merged table and apply a filter to select rows with the value null in the expanded column. These rows are the ones without a match.
#PowerQuery #LeftAntiJoin #Excel