Filters are important part of every Power Query project and should not be underestimated. Let’s have a look on how to easily build & optimize your filters. As usual, the difficulty will increase with every following example.
Level 0: Selective Filters
The easiest option to filter the data is to simply select the desired values. The downside is that the filter will be always looking for the previously selected value, no conditions are applied (click the animation to open it on separate tab):
I recommend not to use such filters often since they are not flexible. One important thing to mention here is the when you look at the generated code, you can see the Power Query automatically adds parentheses around your selection:
I am not quite sure why the parentheses are added but you can easily remove them by directly editing the code. You can also split the code into lines so it’s more readable (add line break by pressing Shift+Enter) . Click the animation to open it on separate tab:
Level 1: Simple Conditional Filters
By default, Power Query offers several filters which will cover 90 % of your needs. You can filter for KWs containing value X, starting with value X, ending with value X and negations of these options. The following animation shows “begins with” filter. Click the animation to open it on separate tab:
As in the Level 0, it’s not difficult to make edits directly in the code. When you read few function names, you will be able to guess other function names. E. g. when you know that there is Text.StartsWith function, there must be also Text.EndsWith function, right? Click the animation to open it on separate tab:
Level 2: Advanced Conditional Filters
When constructing your filter, notice the “Advanced” switch in the filter wizard. Don’t be scared to use it. It allows you to build filters across multiple columns with varying conditions for each column. As usual, you can edit the code, so it’s more readable again 🙂 Click the animation to open it on separate tab:
Level 3: Custom Filters
Until now, the examples were easy because they were based on clicking various UI elements. But what if you want to:
- Filter all keywords which have more than 10 characters
- Filter all keywords which have more than 2 words
- Filter all keywords where the Click/Impression ratio is higher than 30%
- Filter all keywords where the 4th word is equal to “buy”
- Filter all keywords which contain “buy” but do not start with “buy” and have more than 2 clicks
Since filters are similar to constructing “IF” conditions, most of the examples are somewhat similar to conditions used in this article: IF Function in Power Query for Advanced
Filters all keywords which have more than 10 characters
Filter all keywords which have more than 2 words
List functions coming onto the scene:
So at first, you need to split [Keyword] to list (=separate the words), then you count the number of words by using List.Count function around the Text.Split function.
Filter all keywords where the click/impression ratio is higher than 30%
Filter all keywords where the 4th word is equal to “buy”
This is one is fairly complicated. You need to split [Keyword] to list with Text.Split(), then select 1 word from the 4th word via List.Range(), convert that selection to standard text with Text.Combine() and check the result is equal to “buy”. The result:
Filter all keywords which contain “buy” but do NOT start with “buy” and have more than 2 clicks
Notice the use of “not” in the formula – that’s the important piece in this example.
You can see that you can create extremely simple filters as well as extremely complicated ones if you need to. The only limit is your ability to combine various existing Power Query functions. I recommend you to check out IF Functions for Beginners, IF Functions for Advanced and List Functions to get more information about conditions, AND and OR clauses and List functions.