Excel Power Query Value Filtering Explained

How-to basics · Updated November 21, 2026

To filter values in Excel Power Query, open the query in Power Query Editor, select the filter arrow in the column header, and choose the value or condition you want. The filter becomes an applied step, so it can be adjusted later and reapplied when the query refreshes.

What value filtering means in Power Query

Power Query is Excel’s tool for importing and reshaping data before loading the result into a worksheet or the Data Model. Filtering keeps only rows that meet a condition, such as Region equals West, Quantity is greater than 10, or Order Date falls within a chosen period. Rows that do not meet the condition are excluded from the query result.

A Power Query filter does not delete rows from the original workbook, database, or file. It changes the transformation instructions for the query. This distinction matters: if you later remove or change the filter step, the excluded rows can appear again on refresh, provided they are still present in the source.

In Excel for Microsoft 365 on Windows, create or select a query through the Data tab. For a table already in the workbook, select a cell in the table and choose Data > From Table/Range. The Power Query Editor opens, with a preview of the data and an Applied Steps list that records each transformation.

Filter a column by selecting values

The quickest approach is to use the column’s filter menu. It is useful when you want to keep a few known categories, names, codes, or statuses rather than describe a rule such as “greater than.”

  1. In Power Query Editor, locate the column you want to filter and select its header’s filter arrow.
  2. In the value list, clear Select All, then check the values to keep. You can use the search box to find a value in a long list.
  3. Select OK. Power Query adds a filtered-rows step to Applied Steps and updates the preview.
  4. Check the preview, then choose Home > Close & Load to return the results to Excel, or Close & Load To to choose a destination.

For example, selecting only West and Central in a Region column keeps rows for those two regions and excludes East and South. Selecting multiple values means “keep rows matching any of these values,” not “keep rows that match all of them.” If a value you expect is not visible in a large list, try searching for it or use a text, number, or date condition; the menu’s displayed values may reflect the preview rather than every distinct value in a very large source.

Use conditions for text, numbers, and dates

When a checkbox list is inconvenient, choose a condition from the filter menu. The available options depend on the column’s data type. For a text column, use Text Filters; for numbers, use Number Filters; and for dates, use Date Filters. Options commonly include equals, does not equal, begins with, contains, greater than, and between. The condition is evaluated against the column values, not against how the cell happens to look after Excel formatting.

  • Text: To retain rows whose Department is exactly Sales, use Text Filters > Equals and enter Sales. To include descriptions containing the word urgent, use Text Filters > Contains.
  • Numbers: To keep invoice amounts above 500, use Number Filters > Greater Than and enter 500. For a range, use the between condition and specify the lower and upper limits.
  • Dates: Use Date Filters to choose a date condition or a calendar-based period, such as dates before a specified day. Confirm the column is typed as Date or Date/Time if the options look wrong.
  • Blanks and errors: The checkbox list may offer blank or error entries. You can exclude them by unchecking those entries, or choose a suitable condition where available.

Data types are important. A column that looks numeric may actually contain text, especially if it came from a CSV file or contains mixed entries such as “N/A.” In Power Query, inspect the data-type icon at the left of the column name. If needed, select the column and choose Transform > Data Type, then choose the appropriate type. Changing a type can itself produce errors for values that cannot be converted, so inspect the preview after the change.

Combine conditions and filter several columns

Filters on separate columns usually work together. If you filter Region to West and Amount to greater than 500, the result contains rows that satisfy both steps: they are in the West region and their amount exceeds 500. This is an AND relationship between the separate filters.

Within a single condition, Power Query may let you combine rules with And or Or. For example, a custom text filter could keep rows where Status equals Open or Pending. Use Or when either rule is sufficient; use And when a row must satisfy both. Be careful when combining conditions across different columns: two separate filters generally narrow the result rather than create an alternative between columns.

For a more complicated rule, open the filter menu and choose an option such as Text Filters > Custom Filter, when available. The dialog lets you set conditions without writing code. If the desired logic is not available in the dialog, you can use the formula bar or Advanced Editor to edit the query’s M language, but doing so is optional for ordinary filtering.

What a filter step looks like in M

Power Query stores transformations in the M language. A simple exact-value filter may appear in the formula bar as = Table.SelectRows(#"Changed Type", each [Region] = "West"). This means: take the previous step, named Changed Type, and keep rows whose Region value equals West. The formula bar and Advanced Editor are useful for understanding or refining a query, but the header menus create the same kind of step without requiring you to write M.

The step name in the example depends on the earlier steps in your query, so it may differ in your file. M also distinguishes text, numbers, dates, and null values. A text value such as “500” is not necessarily the same as the number 500. If you edit M directly, preserve the correct column name and data type, and use the editor’s error messages to locate syntax or conversion problems.

Refresh behavior and changing source values

After loading the query, choose Data > Refresh All to run its steps again against the source. A filter step is normally reapplied during refresh. If the source gains a new row with Region set to West, that row will be included by a filter that keeps West. A new value that is not selected in a fixed checkbox filter will remain excluded until you edit the filter.

This makes the distinction between a fixed selection and a rule especially useful. Selecting the current values West and Central keeps those categories, but does not automatically include a newly introduced category called North. A condition such as Amount greater than 500 can include new rows that meet the rule. Choose the kind of filter based on whether your requirement is a stable list of allowed values or a changing business rule.

Refresh also depends on access to the source. A workbook query may prompt for credentials or fail if a file was moved, a connection is unavailable, or a source column changed. Filtering does not repair a broken connection; check the query’s source and refresh error separately.

Step order, query folding, and performance

The order of transformations can affect both the result and how quickly it is produced. For instance, changing a column’s type before applying a numeric filter helps ensure Power Query compares numbers rather than text. Removing unwanted rows early can reduce the amount of data processed by later steps, particularly when the source is large.

Some data sources support query folding: Power Query can translate steps such as filtering into instructions the source system runs. When folding is available, the source may return fewer rows to Excel, which can improve refresh performance. Not every connector or transformation supports folding, and a step can affect whether later steps fold. For typical worksheet-sized data, a filter is still useful even when folding is unavailable.

Do not assume that seeing a smaller preview means the original source was changed or that all processing happened at the source. The query preview shows the result of the steps; the source remains separate unless you explicitly edit it through another process.

Common filtering problems and fixes

  • A value is missing from the filter list: Search for it, check the source and preview, and verify that an earlier step has not removed it. For a large list, filter with a condition rather than relying only on the visible checklist.
  • A number filter behaves like text: Check the data-type icon and convert the column to a numeric type. Look for spaces, symbols, or mixed values that prevent conversion.
  • Dates appear in an unexpected order: Confirm the type is Date or Date/Time, not Text. If imported dates are ambiguous, the source’s regional format may affect how they are interpreted; use the appropriate locale when changing the type.
  • Blank rows remain or disappear unexpectedly: Determine whether the cells contain true nulls, empty text, or spaces. These may look alike in the preview but behave differently in filters.
  • The result is empty: Review the condition, spelling, data type, and earlier steps. An exact text match can exclude values with extra spaces or different wording.
  • Refresh changes the result: Compare the source data with the filter rule. A fixed list of selected categories will not include new categories automatically, while a rule may include new values that meet its criteria.

Power Query filters versus worksheet filters

Excel worksheet filters, available from a table header or through Data > Filter, hide rows in the current worksheet view. They are convenient for quick inspection, but they do not create a reusable import-and-cleaning process. Power Query filters are saved as transformation steps and run again when the query refreshes, making them better suited to repeatable data preparation.

Use a worksheet filter when you want to temporarily examine records already loaded to a sheet. Use Power Query when you need to import or clean data consistently, combine filtering with other transformations, or refresh the output from an updated source. Both can be useful: a Power Query result can also be filtered on the worksheet after it is loaded, but that worksheet view filter is separate from the query’s filtering steps.

Frequently Asked Questions

How do I filter for one exact value in Power Query?
Open the column-header filter menu, clear Select All, select the value to keep, and choose OK. For a condition-based approach, use the appropriate Text, Number, or Date Filters menu and choose Equals.

Can I filter for more than one value?
Yes. In the checkbox list, select each value you want to retain. Power Query keeps rows matching any selected value in that column. Filters applied to separate columns generally narrow the result together.

Does filtering in Power Query delete the source rows?
No. It excludes rows from the query output; it does not delete them from the source file or table. The source rows can appear again if you change or remove the filter step.

How do I keep blank values or remove them?
Open the column’s filter menu and check or uncheck the blank entry if it is shown. If blanks behave unexpectedly, inspect whether the data contains nulls, empty strings, or spaces, since these are not always equivalent.

Will new values be included after a refresh?
It depends on the filter. A rule such as Amount greater than 500 can include new rows that meet it. A checklist that selects only specific categories will not automatically select a newly introduced category.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.