At times, you may need to exclude a large subset of rows from your results. Instead of adding multiple filters to your chart, you can exclude all results from a certain row by doing an “anti-join”.
In this example, we created a custom column including eight cities we would like to exclude from our results.
- Add the column with the results you would like to exclude in your first dataset. You will need to add the same column twice as we’ll be using the second column as a filter.
- Add a second dataset with the column you would like filtered. Add this column twice in the second dataset.
- Select Outer Join on the first column. The middle column will now include results you’re looking to exclude.
- Add a Filter Pipeline step to exclude any rows with results in the second column. Here, we will exclude any rows where the result “is not null” in our column ‘City_2’.
- You can now hide the columns ‘City_2’ and ‘City_2:1’ in the data pipeline to only keep your ‘City_1’ column.
We now have successfully removed the cities in the custom column and have 1,205 rows instead of 1,213 rows!