Filtering in Power Query reduces the table to include only those items which meet specific criteria. However, Power Query doesn’t have an option to filter to include all items. In Excel, we can use the asterisk ( * ) as a wildcard character to match any text. Therefore, if we want to include all items in a Filter, we just use a single asterisk. However, Power Query doesn’t have equivalent functionality. So, in this post, we will look at a method to create something similar to filter All in Power Query.
This post is in response to a question from an academy member who was asking about filtering a table.
“I need an ‘all’ option…so if they don’t select a name, then they get all the records.”
OK, let’s see how we can solve this.
Download the file: 0071 Filter All in Power Query.zip
Watch the video
To demonstrate the approach, I have created a workbook containing two filters.
- Account allows us to select an account code
- Category allows us to select a category
The workbook includes a query linked to an external CSV file and loaded into a Table. The CSV file is included in the download file. To work along with the example, you will need to re-point the query to the source CSV file on your PC.
In the example workbook, these filters don’t currently do anything. But we want to create the functionality to filter by Account or Category, but if either is left blank, it will return all the items for that item.
Adding the filters to Power Query
The first step is to create two named ranges:
- Cell C4 = AccountFilter
- Cell D4 = CategoryFilter
Now let’s add both of these named ranges into Power Query as parameters
- Select cell D4 and click Data > From Table/Range from the ribbon
- Change the data type to text
- Replace the null values with a blank text string by clicking Transform > Replace Values
- Right-click on the blank cell and select Drill Down to get to a single value.
Note: If the cell filter was blank, you will not see any values on your screen (that’s fine, don’t worry)
- Close and load this query into Excel as a Connection only.
Repeat these steps for the AccountFilter in cell D4.
Applying the filter in Power Query
Go back into Power Query, in the source query, select the Account column and apply a text filter; use any text, it doesn’t matter.
Next, in the Formula Bar, change the filter text string to be the AccountFilter query. To do this, change the following:
= Table.SelectRows(#"Changed Type", each ([Account] = "Accounts Payable"))
= Table.SelectRows(#"Changed Type", each ([Account] = AccountFilter))
If the filter was empty, the screen will now look like the following:
If you are working along with the example, the applied steps contain the following items:
We can now add logic to the applied steps to return the filtered or unfiltered tables.
Click the Fx symbol to create a new step. Enter the following statement into the formula bar.
= if AccountFilter = "" then #"Changed Type" else #"Filtered Rows"
This is simply saying that if the AccountFilter is a blank text string, then return the table after Changed Type, (which is unfiltered), otherwise return the table after Filtered Rows (which is filtered). This creates the effect of a blank filter returning all the items.
Repeat the actions above for the Category column and the CategoryFilter query.
Before creating the second if statement, the applied steps will be:
Therefore, the if statement will be:
= if CategoryFilter = "" then Custom1 else #"Filtered Rows1"
Finally, close and load the Source query as an Excel table.
Testing the solution
Now, all we need to do is test it out. First, enter some values into either filter box, then click Data > Refresh All from the ribbon.
If either or both cells are blank, no filter is applied to the final table for those filters.
The screenshot below shows a filter on the Category, but no filter applied to the Account column.
I have added a refresh macro button and data validation drop-down lists in the example file. However, the data validation list illustrates how the solution might work; it is not dynamic, so it will not pick up any extra values.
Power Query doesn’t have filtering by wildcards for us to include all items. But with a little bit of logic, we can create a similar effect.
And maybe in the process, you’ve learned that you can type directly into the formula bar and easily return the query’s status after each applied step.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
3 thoughts on “Filter All in Power Query”
Thanks Tanya 🙂
Very nice tip! Thanks…