Power Query Filter: How to Filter Rows in Excel

To filter in Power Query, open the query in Power Query Editor, select the filter arrow in a column header, choose the values or conditions to keep, and select OK. Power Query records the filter as a query step, so the filtered rows are excluded from the output when you load or refresh the query.

What a Power Query filter does

Power Query is Excel’s tool for importing, shaping, and combining data. A filter narrows the rows that continue through a query. For example, if a sales table has several years of transactions, you can filter its Date column to keep only the year you need before loading the result into a worksheet or Data Model.

A Power Query filter changes the query’s output, not the original source table. If the source is an Excel table, a CSV file, or a database, filtering in the query does not delete matching records from that source. It determines which records are included in the result produced by the query. This distinction matters when other people or processes still need the complete source data.

Filters are also different from filtering a worksheet table with Excel’s regular AutoFilter. A worksheet filter temporarily hides rows in the displayed range; it does not normally change which rows the query loads. A Power Query filter is part of the data-preparation process and is applied again when the query refreshes.

How to add a filter in Excel

In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range to create a query. If the query already exists, open Data > Queries & Connections, right-click the query, and choose Edit. These actions open Power Query Editor.

Find the column you want to filter and select its header’s drop-down arrow.
For a list of distinct values, clear Select All, check the values to keep, and select OK. Use the search box in the menu to find a value in a long list.
For a condition, open the relevant filter category, such as Text Filters, Number Filters, or Date Filters, and choose an option such as Equals, Begins With, or Greater Than.
Enter the comparison value or values in the dialog and confirm the filter.
Review the result, then select Home > Close & Load to return the query output to Excel. Use Close & Load To if you need to choose a destination or load option.

After applying a filter, the column’s filter icon indicates that a filter is active. Power Query Editor also lists the new operation under Applied Steps. Selecting a step lets you inspect the result at that point in the transformation sequence; selecting its gear icon, when available, lets you edit its settings. To remove a filter, use the X beside its step in Applied Steps, or reopen the column menu and clear the filter.

Choose a filter that matches the column

The filter menu depends on the column’s data type. Power Query may interpret a column as text, a whole or decimal number, a date, a time, or another type. Check the small type icon beside the column name if a filter behaves unexpectedly: values that look like dates may actually have been imported as text.

Text: Keep or exclude named values, or use conditions such as Equals, Contains, Begins With, or Ends With. These are useful for narrowing a product list to a category label or finding rows with a particular code.
Numbers: Use comparisons or ranges, such as keeping amounts greater than a threshold. A condition keeps rows that meet the rule rather than asking you to tick each distinct amount individually.
Dates: Select particular dates or apply a date condition, such as keeping dates after a cutoff. Confirm that the column has a date or date/time type so comparisons operate on dates rather than text strings.
Blanks and nulls: The value list can include blank-looking entries. In imported data, a missing value may be represented as null, while an empty text value can be different. If the results matter, inspect the column and test the intended case rather than assuming every blank-looking cell is identical.

For instance, suppose a sales query has Status and Amount columns. Filtering Status to Open keeps open records; filtering Amount to values above 1,000 keeps higher-value records. Applying both filters keeps only rows satisfying both conditions. If the Amount column contains text such as “unknown,” convert or clean the data deliberately before relying on a numeric comparison.

Combine filters and understand their order

Filters on separate columns generally work together: a row must pass each active filter to remain in the result. If you keep Region = West and Status = Open, a row must match both. Within a single filter condition, the choices you make determine whether values are treated as alternatives or as combined requirements. For example, selecting West and East from the list keeps either region, not just rows that are simultaneously in both regions.

Power Query applies transformations in the order shown under Applied Steps. The order can affect the data available to later operations. A filter added before a grouping operation limits which rows contribute to the groups; a filter added after grouping limits the grouped results. Likewise, filtering before removing duplicates can produce a different result from filtering afterward. If the output seems surprising, click through the steps in order and inspect where the rows change.

When conditions become more involved, use the filter menu’s custom condition dialog, where available, or the formula bar and Advanced Editor to review the generated M expression. Be clear about whether a rule means “A and B” or “A or B.” For example, keeping rows where a person is in the West region and has an open status is narrower than keeping rows where the region is West or the status is Open.

Filtering versus removing rows

A column filter is usually the clearest choice when the rule is based on values in a field: keep dates in a period, exclude a status, or retain amounts above a limit. Other row operations serve different purposes. Power Query’s Home tab includes options for keeping or removing rows by position, such as keeping the top rows or removing duplicates. Those actions do not mean the same thing as filtering for a value.

For example, removing the top 10 rows means discarding rows by their position at that stage of the query. It does not mean excluding records whose value is 10 or less. Similarly, Remove Duplicates selects which repeated records to retain according to the columns selected; it is not a filter that keeps every record matching a particular category. Choose the operation that describes the rule you intend, and check Applied Steps afterward.

The M code behind a filter

Power Query stores transformations in the M language. A filter created from the column menu becomes a step that evaluates a condition for each row. For example, if the prior step is named Source and the table has a Status column, the following M expression keeps rows whose status is Open:

Table.SelectRows(Source, each [Status] = "Open")

A numeric condition can be combined with it. If Source has Region and Amount columns, this expression keeps rows where Region is West and Amount is at least 1,000:

Table.SelectRows(Source, each [Region] = "West" and [Amount] >= 1000)

These are M expressions, not Excel worksheet formulas. In the editor, a complete query commonly uses a let block to name steps and an in line to return the final step. The graphical interface is sufficient for many filters; reading the M code is useful when you need to understand a complex condition, troubleshoot a step, or make a precise edit. Avoid editing code casually if later steps depend on the existing step names.

Refresh behavior and query folding

Once a filter is part of the query, refresh applies it to the current source data. If a new row is added to an Excel table and meets the filter, it can appear in the refreshed output; a row that fails the condition remains excluded. The query does not necessarily update the result immediately after you change the source. Use Data > Refresh All, or refresh the individual query from Queries & Connections, to request updated results.

For some data sources, Power Query can translate certain steps into instructions the source system runs. This is called query folding. When folding is supported and maintained, filtering may reduce the amount of data transferred to Excel because the source can return only matching rows. Folding depends on the connector, source, and transformations used; it is not guaranteed for every query. A filter’s presence alone does not prove that it is being executed by the source.

For large sources, filtering early can be helpful, particularly when the source supports folding. However, do not assume that every step should be moved ahead of every other step: an earlier filter can change the meaning of later calculations. Confirm the intended result first, then consider performance and whether the source supports the transformation.

Common filter problems and fixes

The expected values are missing: Check whether an earlier step filtered or transformed the column. Inspect Applied Steps from top to bottom, and confirm that the source data actually contains the values.
A number or date filter acts oddly: Check the column’s data type. Text that resembles a number may sort alphabetically, and text that resembles a date may not compare chronologically. Convert the type only after considering whether all values can be converted safely.
New records do not appear: Refresh the query and verify that the source range includes the added records. A query built from a fixed range may not include rows entered outside that range; an Excel Table generally expands as data is added directly below it.
Too many rows disappear: Review filters on every column, not only the one you just changed. Multiple filters usually intersect, so each additional condition can further narrow the output.
The filter step shows an error: A later type conversion or a changed source column name can break a step. Check the error details and the step immediately before the failure. Correct the source or update the affected step rather than simply removing a condition whose purpose is important.

Power Query capabilities and ribbon layouts can differ between Excel for Windows, Excel for Mac, and Excel for the web, and support also depends on the connector and build. The steps above describe Excel for Microsoft 365 on Windows. If a menu or editing option is absent in another edition, check that edition’s current Power Query support rather than assuming the Windows interface is identical.

Frequently Asked Questions

How do I filter rows in Power Query?
Open the query in Power Query Editor, select the drop-down arrow in the target column header, choose values or a condition, and select OK. The filter appears as a step under Applied Steps.

Does a Power Query filter delete rows from my source?
No. It excludes rows from the query result. The source file, table, or database is not changed by the filter itself.

How do I remove a filter in Power Query?
In Power Query Editor, remove the relevant filter step using the X beside it in Applied Steps. You can also open the column’s filter menu and clear its filter, depending on the filter type and interface.

Can I filter a column by two conditions?
Yes. Use a custom filter condition when the menu provides one, or add conditions through M. Check whether the logic should use AND, which requires both conditions, or OR, which accepts either condition.

Will a filter apply to new data after refresh?
Yes, if the refreshed query reads the new rows from its source and those rows meet the filter. Refresh the query and check that its source range or table includes the new data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.