Power Query Problems

How to Filter Values Beginning with a Specific Keyword in Excel Power Query

Amos Gikunda Oct 12, 2026 871 views

Question details

The user wants to filter a dataset in Power Query to either include or exclude values that start with a specific keyword.

How to Filter Values Beginning with a Specific Keyword in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Cleaning up a dataset by keeping or removing rows where a specific column's text begins with a certain word or phrase, such as 'Exemption'.
Observed behavior: The user needs a built-in way to filter prefixes without writing complex custom transformation formulas or M-code.

Before you start

Ensure your data is loaded into the Power Query Editor and that the column you intend to filter is correctly formatted as Text.

Solution 1Recommended

Use Built-in Text Filters for Prefixes

Use the built-in Text Filters option in Power Query for a quick and simple way to filter out or include rows based on a starting keyword.

Power Query provides native text filtering options that handle common text matching scenarios natively. Using these built-in functions is highly recommended over creating custom conditional columns, as it keeps your applied steps clean and processes faster.

Open Power Query Editor

Launch the Power Query Editor and locate the specific text column you want to filter in your dataset.

Open Filter Menu

Click the drop-down arrow icon located on the right side of the target column's header.

Navigate to Text Filters

Hover over the 'Text Filters' option in the drop-down menu to reveal the secondary flyout menu.

Select the Appropriate Filter

Select 'Begins With...' to keep rows starting with your keyword. If you want to exclude those matching values instead, choose 'Does Not Begin With...'.

Enter the Keyword

In the Filter Rows dialog box that appears, enter your desired keyword (e.g., 'Exemption') into the input field and click 'OK' to apply the step.

Avoid Custom Formulas: Using this built-in feature saves time and entirely avoids the need to write complex M-code or custom transformation formulas for a simple prefix filter.

Filter Data Easily in WPS Office

Filter Values Beginning with a Keyword in WPS Spreadsheet

You do not always need complex data querying tools for routine tasks. WPS Spreadsheet provides an intuitive AutoFilter feature that lets you easily include or exclude values beginning with specific text directly in your worksheet.

1. Open Your Data: Open your dataset or spreadsheet file using WPS Spreadsheet.
2. Enable AutoFilter: Highlight your data headers, go to the 'Data' tab on the top ribbon, and click 'AutoFilter'.
3. Access Text Filters: Click the newly appeared drop-down arrow on your target column's header and select 'Text Filters'.
4. Choose Prefix Option: Choose 'Begins With...' to show only those values, or 'Does Not Begin With...' to exclude them.
5. Apply Filter: Enter your keyword into the text box and click 'OK' to instantly filter your worksheet data.

Free and lightweight alternative to Microsoft ExcelHighly compatible with Microsoft Excel (.xlsx) data and formatsIntuitive user interface for quick, immediate data filteringNo need to learn complex M-code or navigate separate editor windows

microsoft office alternative - wps office

Frequently Asked Questions

Does the 'Begins With' filter in Power Query differentiate between uppercase and lowercase letters?

Yes, Power Query is strictly case-sensitive. The keyword 'Exemption' and 'exemption' will be treated as different values. You may want to transform the column to lowercase or uppercase first if your data formatting is inconsistent.

Can I filter by multiple starting keywords at the same time?

Yes. In the Text Filter dialog box, you can select the 'Advanced' radio button to add multiple clauses. This allows you to filter rows that begin with one keyword OR begin with a different keyword simultaneously.

What if my keyword is located somewhere in the middle of the text?

If the keyword is located within the text rather than at the absolute start, you should use the 'Contains' or 'Does Not Contain' option from the Text Filters menu instead of the 'Begins With' option.

Excel & Data Problems