To filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the table, choose Data > From Table/Range, then use the filter arrow beside a column heading in the Power Query Editor. Choose the values or conditions to keep, then select Home > Close & Load; the filtered result is loaded to Excel, and the filter is applied again when you refresh the query.
What a Power Query table filter does
A Power Query filter is a transformation in a query, not just a temporary display setting on an Excel worksheet. It tells the query which source rows to retain in its result. For example, filtering a sales table to show only the West region removes other regions from the query output, while leaving the original source table unchanged.
This distinction matters when you share or refresh a workbook. A worksheet’s AutoFilter hides rows in the current view; a Power Query filter shapes the data that is loaded from the query. The filter becomes one of the query’s recorded steps, so it can be reviewed, changed, reordered, or removed later.
Power Query is useful when the same filtering work must be repeated, when source data is replaced or updated, or when several cleanup steps need to be documented together. It is not necessary for every one-off sort or filter: Excel’s ordinary table filter may be quicker if you only need to inspect existing rows without creating a reusable import process.
How to filter an Excel table in Power Query
- Make the source a table. If the data is not already an Excel table, select a cell in the range and choose Insert > Table. Confirm whether the first row contains headers.
- Open the query editor. Select a cell in the table and choose Data > From Table/Range. Excel opens the Power Query Editor with the table as the query source.
- Choose the column to filter. Select the arrow in the column header. For a short list of known values, select or clear checkboxes. For a larger or changing set, use the relevant text, number, or date filter command.
- Set the condition. For example, choose Text Filters > Begins With and enter a prefix, or use a number filter such as Greater Than. The available filter choices depend on the column’s data type.
- Check the result and load it. Confirm that the preview contains the intended records, then choose Home > Close & Load. Excel loads the result, usually as a new worksheet table unless you choose a different load destination.
The filter arrow’s value list is convenient for simple selections, but condition-based filters are often more maintainable. If next month’s data might include new regions, a rule such as “Region equals West” is clearer than relying on a manually selected set of currently visible values.
Filter by text, numbers, dates, and blanks
Power Query offers filters suited to the selected column’s data type. Text columns can be filtered by exact values or conditions such as contains, begins with, and ends with. Number columns support comparisons such as equals, does not equal, greater than, and less than. Date and date/time columns offer date-based conditions, including comparisons against a date or range.
For example, a support team might filter a table to keep rows where Status equals Open. A purchasing report could keep Unit Price greater than 100, while a monthly report could retain dates from the relevant period. The filter operates on the actual values in the query, so check that dates and numbers have been recognized as dates and numbers rather than text.
Blank values can be included or excluded through the column’s filter list. Power Query may distinguish a true null value from an empty text value, so a blank-looking cell is not always the same thing as a missing value. If a filter behaves unexpectedly, inspect the column’s type and the source values; trimming spaces or replacing errors may be necessary before filtering.
Combine conditions across columns
Filters on different columns are generally combined as successive steps. For instance, filtering Region to West and Status to Open keeps rows that satisfy both conditions. In logical terms, that is an AND: a row must pass the Region condition and the Status condition to remain.
Within one column, selecting several distinct values from the checkbox list keeps rows matching any selected value. Selecting West or East in Region therefore works like an OR. For more precise rules, use a column’s custom filter dialog, where two conditions can be joined with And or Or. Read the selected operator carefully: “Status is Open OR Status is Pending” retains more rows than “Status is Open AND Status is Pending,” which normally cannot be true for a single-valued status field.
When the logic becomes complicated, break it into readable steps or add a helper column rather than building an opaque filter. A clear query is easier for another workbook user to audit and less likely to exclude records by accident.
What the filter looks like in Power Query M
Power Query records transformations in its M language. You do not need to write M to use the filter menus, but the formula bar or Advanced Editor can help explain what a step does. A simple step that retains rows where Region is West can look like this:
= Table.SelectRows(#"Changed Type", each [Region] = "West")
Here, Table.SelectRows evaluates a condition for each row, and the preceding step is named Changed Type. Your step names and column names may differ. If a column name contains spaces or special characters, Power Query’s generated M syntax may represent it differently, so copying this example without adjusting names can produce an error.
Use the interface first if you are learning: it creates valid M steps and preserves the query’s step sequence. Advanced Editor is useful for deliberate edits, but a small syntax error can stop the query from refreshing. The M expression is not an Excel worksheet formula and should not be entered into a cell.
Refresh behavior and where results go
After loading, the filtered table is a query result. If rows are added to or changed in the source table, refresh the query to read the updated source and apply its filter steps again. In Windows Excel, use Data > Refresh All to refresh workbook connections, or right-click the query output and choose Refresh when available. The exact refresh controls can vary with the workbook and connection.
A filter does not usually rewrite or delete records in the source table. It changes which records appear in the query output. Keep the source and output clearly labeled, especially if both are on the same worksheet, to avoid editing the loaded result while expecting the source to change.
You can manage query loading from the Queries & Connections pane, including choosing whether a query loads to a worksheet or is used as a connection for another query. If you change the source table’s name, move the workbook, or alter a file path, the query may need its source step updated; refreshing cannot repair a source location that no longer exists.
Power Query filters versus Excel table filters
An Excel table filter is best for interactively hiding rows in a table already on the sheet. It is immediate and easy to toggle, but it does not by itself create a repeatable import-and-cleanup sequence. A Power Query filter is better when you want a reusable rule that runs again during refresh and forms part of a larger process such as changing data types, removing columns, or combining files.
The two can also be used together, but they affect different layers. A Power Query filter determines which rows are loaded. A worksheet filter applied afterward controls which of those loaded rows are currently visible. Clearing the worksheet filter will not restore records that the query excluded; edit the query filter and refresh to bring those rows back.
Choose based on the job, not on which feature sounds more advanced. For a quick check of a small table, use its header filter. For a recurring report that imports or cleans data, use Power Query. For a one-time analysis that requires formulas or pivoting, a worksheet table, PivotTable, or another Excel tool may be more direct.
Common filtering problems and fixes
- Expected rows are missing. Open the filter step and check its condition, the selected values, and earlier steps. A previous step may already have removed or transformed the rows.
- A number or date filter is unavailable. The column may be typed as text. Select the column and set an appropriate type from Transform > Data Type, then review the filter.
- Refresh shows an error. Check whether the source table, file path, column name, or data format changed. A query step that refers to a renamed column can fail even if the source still contains the records.
- A blank is retained or removed unexpectedly. Check for nulls, empty strings, spaces, and errors rather than assuming all blank-looking cells are identical.
- The output seems to ignore a filter change. Make sure the change was made in the Power Query Editor and then applied with Close & Load. If the query output is already loaded, refresh it after changing the query.
For an audit, select steps in the Query Settings pane and watch how the preview changes. This makes it easier to find whether the problem begins at the filter itself or earlier in the transformation sequence. Keep a copy of important source data before making broad cleanup changes, even though ordinary query filtering does not delete the source records.
Mac and Excel for the web
The menu instructions above target Microsoft 365 Excel on Windows. Power Query is also available in Microsoft 365 for Mac, but the supported connectors and authoring capabilities can differ by Excel version and update channel; menu names and editor features may not match Windows exactly. Excel for the web also has a more limited Power Query experience than desktop Excel. If you cannot find From Table/Range or the required editor command, use the current desktop version and check Microsoft’s feature documentation for your platform.
Frequently Asked Questions
Does a Power Query filter delete rows from my Excel table?
No. It filters the query result; it does not normally delete matching or nonmatching rows from the original source table. The loaded output contains only the rows that pass the query’s filter steps.
Will the filter still work when I add new rows?
Yes, if the new rows are included in the query’s source table or range and you refresh the query. The filter rule is reapplied to the refreshed data, so a condition such as Region equals West can also retain later West records.
Can I filter on more than one column?
Yes. Apply a filter to each relevant column. Filters on separate columns normally narrow the result together, so a row must meet each condition to remain.
How do I remove a Power Query filter?
In the Power Query Editor, find the filter step in Query Settings under Applied Steps. Select the step’s delete control, or edit the column filter to include the values you need, then choose Close & Load. Removing the step can restore rows that earlier steps have not excluded.
Why does clearing the worksheet filter not bring back every row?
A worksheet filter only changes what is visible in the loaded table. If Power Query excluded rows before loading, clearing the worksheet filter cannot recover them. Change the query filter and refresh the output.
Related
- Excel Paste Numbers as Text? Keep IDs, Zeros, and Codes
- Excel Paste in Reverse Order: Practical Methods
- Excel Paste Keyboard Shortcuts: Copy, Paste Special & More
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- INDEX MATCH With Multiple Criteria: Excel ExamplesIn Excel for Microsoft 365, use INDEX with MATCH and multiply the criteria tests to find a row…
- Link Different Sheets in Excel with FormulasTo link a cell on one Excel sheet to a cell on another, enter a reference such as…
- Excel TEXT Function Format Options and Examples ExplainedExcel’s TEXT function converts a number, date, or time into text displayed with a format code: use =TEXT(value,…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- Average Filtered Column in Excel: Formula and TipsTo average only the visible rows in a filtered Excel column, use =SUBTOTAL(1,B2:B100), replacing B2:B100 with your data…
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.