Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Filter All in Power Query

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 Icon
Download the file: 0071 Filter All in Power Query.zip

Watch the video


Watch the video on YouTube.

The scenario

To demonstrate the approach, I have created a workbook containing two filters.

Filter Drop-Down

  • 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

Creating named ranges

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
    Data from Table or Range
  • Change the data type to text
  • Replace the null values with a blank text string by clicking Transform > Replace Values
    Transform replace values
  • Right-click on the blank cell and select Drill Down to get to a single value.
    Right-click drill down
    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.

Filter by an item

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"))

To this:

= Table.SelectRows(#"Changed Type", each ([Account] = AccountFilter))

If the filter was empty, the screen will now look like the following:

After filter applied

If you are working along with the example, the applied steps contain the following items:

Applied Steps window

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.

FX icon in Power Query window

= 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:

Applied steps after second filter

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.

Power Query Filter All option

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.

Conclusion

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.


Headshot Round

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:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
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

Leave a Reply

Your email address will not be published. Required fields are marked *