Power Query gives users several different options for filtering. The simplest method is to apply a filter similar to how you would in Excel while more advanced options allow you to filter for multiple values or based on specific conditions. We’ll explain the simple to advanced methods for working with Power Query.
Power Query is a fantastic tool provided by Microsoft and it spans several different pieces of software. Power Query is available in Excel, Power BI, Data Flows Gen2, and Data Wrangler. We’ll cover how to apply basic filters for a single value, multiple values, simple to complex conditional columns, as well as how to filter down to a specific row.
Let’s jump in!
Table of Contents
How to Filter Data in Power Query
To filter data in Power Query, click on the upside-down triangle at the top of a column header. This expands the filter menu where you can select specific values, or apply more advanced filtering criteria. After making your selection, press OK to apply the filter.
From the dropdown, you can select specific values, similar to how you would filter using Excel. You can also apply basic conditional filtering options by clicking on Text Filters, Number Filters, or Date/Time Filters depending on the data type of the column you are working with.
Note: You can apply multiple filters to multiple columns in your dataset. After applying your first filter, the second filter will be subject to the data that’s already been filtered in the first. Because of this, pay attention to the order that you apply filters across columns to ensure that the filtered results are as expected.
The Importance of Data Types When Filtering in Power Query
When applying filters, it’s important to note that Data Types play an important role in the filtering options that are available. You’ll notice in the screenshot above, that there is a section called Text Filters. this is determined by the data type assigned to the specific column. If we look at the Sales Dollars filtering options, they are called Number Filters.
Power Query automatically gives you options based on the data type of a column. For example, text columns have options to filter based on begins with, ends with, contains, does not contain. Numeric columns let you filter on greater than, less than or between.
If the filtering options aren’t showing up that you would expect, check the data type notated by the icon to the left of the column.
Change data types by right clicking on a column title and selecting a new data type. It will provide a different set of filtering options.
How to Filter Multiple Values in Power Query
To filter by multiple values in Power Query, look for the upside-down triangle next to a column’s title to access the filter options. If you want to start from scratch, click Select All to clear all selections. Next, check the boxes next to each one to include or exclude them from your dataset.
When working with large sets of data, you can use the Search box to type in criteria and it will limit the list of values shown. This can be helpful for finding the values that you want to filter on without having to manually go through an extra long list of values.
How to Filter Rows Based on Conditions in Power Query
Power Query provides two options to filter based on conditions. The first is to use the built in filter conditions by clicking the upside-down arrow next to a column header and go to Number Filters, Text Filters, or Date/Time Filters depending on your column type. For more advanced filtering, you will need to create a conditional column.
The following example shows conditional formatting options that are available from the regular filter menu. The exact options change based on the data type of the column being selected.
The second option to create advanced conditional filter is to utilize a conditional column.
Filtering Based on a Conditional Column in Power Query
For advanced conditional filtering in Power Query, first create a Conditional Column. Enter in the required conditional criteria, and then output a number or letter that can be filtered on if the values are true and return a null or different value if the conditions are false.
The following screenshot shows a conditional column being created with multiple criteria. The important part is that the Output when values are true is the number 1 otherwise it is a null. The value must be consistent to make the second filtering step easy.
The results of our conditional column is a new custom column that we can use for filtering. Applying a basic filter to exclude anything that is null and Power Query will show only the values that meet the conditions set in the conditional column.
For scenarios where filtering based on conditional logic is not possible, it can be advantageous to use a Excel spreadsheet, CSV file or other data source that acts as your filtering criteria.
How to Filter by a List in Power Query
To filter based on a list in Power Query, use the Merge function to combine the list with the original dataset. You can tell Power Query which columns to use to match based off, and you can also tell Power Query to only show rows of your dataset that were matches.
To get started, import a second dataset into Power Query using the Get Data function. For our example, we’ll have a one column index of the items that we want to filter based on. This could be multiple columns as well.
On the left side of Power Query you’ll see that we have two datasets now, the original larger dataset and now a filtering dataset.
We need to Merge these two datasets together.
Navigate back to the original dataset, go to the Home section of the Power Query Ribbon, and Press Merge Queries. You have the option to Merge Queries or Merge as New. Selecting Merge as New will create a 3rd table and leave the original two intact.
For our example, we will choose to Merge Queries.
After selecting to Merge, the Merge Query dialog box will appear. This is the section where you will define which columns to use as the filtering criteria. When the criteria match, the dataset will only return values that are the same on both tables.
In the bottom left, change the Join Kind to Inner. This ensures that only rows that match the selected criteria on both tables are displayed.
Note: Merging is an incredibly useful tool in Power Query. It’s also the same method that you would use to re-create the effect of a VLOOKUP or to combine two tables based on a common value.
How to Filter for a Specific Row in Power Query
To filter on a specific row in Power Query, first add an Index Column. Index columns will fill down in your dataset giving each row a unique number to identify it. Then apply a basic filter on the Index column to only show the specific row desired.
It’s worth noting that this method is only appropriate for datasets where you know that a value will be in the same place. If the number of rows shrinks or expands your filter will be through off, and the creation of conditional filtering logic may be more appropriate. In this case you could also try to concatenate multiple columns to get a specific value to filter on.
From applying basic filters to a column, selecting multiple values, setting up conditions for more targeted data retrieval, to merging with lists and isolating specific rows, Filtering in Power Query is a relatively straightforward process, but it’s true power is in its thoughtful application.
Basic filtering can be done quickly by clicking on the triangle icon next to a title, you can filter on a single value, multiple values, or apply simple conditional logic. For more advanced conditional filtering, create a conditional column. When no logic is available, you can alternatively use an inner join to merge two datasets together or add an index column and specifically filter on a row number.