How to Filter Power Query Text by a List of Partial Keywords in Excel
Question details
The user needs to filter a large dataset of text rows against an editable list of partial keywords to match text variations.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Matching various naming conventions, spellings, and abbreviations (e.g., Amzn US, Amazon Germany, Bosch, BSH) against a large dataset without writing hundreds of individual filter rules.
- Observed behavior
- The user wants a query output that returns only the rows containing at least one of the defined partial keywords from the list.
Ensure that both your main dataset and your keyword list are formatted as official Excel Tables before loading them into the Power Query Editor.
Use Text.Contains for Explicit Partial Matching
This is the most accurate method to check if any text string contains at least one keyword from your predefined list.
By utilizing the Text.Contains function wrapped inside a List.AnyTrue function, Power Query iterates through your keyword list for every row in your main dataset.
To prevent capitalization mismatches, it is highly recommended to add the Comparer.OrdinalIgnoreCase parameter.
Select your main data table, go to the 'Data' tab on the ribbon, and click 'From Table/Range'. Repeat this step for your keyword list table. Ensure your keyword query is loaded as a List (right-click the keyword column header and select 'Drill Down').
In your main data query, go to 'Add Column' > 'Custom Column'. Name the column 'KeywordMatch'.
In the custom column formula box, enter: List.AnyTrue(List.Transform(KeywordList, each Text.Contains([YourTextColumn], _, Comparer.OrdinalIgnoreCase))). Replace 'KeywordList' with the name of your keyword query and 'YourTextColumn' with the name of your data column.
The new custom column will output 'TRUE' or 'FALSE'. Click the filter dropdown on the 'KeywordMatch' column and uncheck 'FALSE' to keep only the rows that match your partial keywords.
Use Table.FuzzyNestedJoin for Spelling Variations
Fuzzy matching is helpful when dealing with unpredictable typos or slight spelling variations rather than exact partial strings.
Handle Complex Data Filtering Easily with WPS Office
If you find Power Query's M-code syntax too complex or need a lightweight, free alternative for your daily data filtering tasks, WPS Office Spreadsheet is an excellent choice. It offers powerful built-in spreadsheet functions and advanced filtering tools while maintaining full compatibility with Microsoft Excel formats.
- 1. Download and Install: Download WPS Office for free from the official website and follow the installation wizard.
- 2. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook containing your data and keywords.
- 3. Use built-in functions: Combine standard spreadsheet functions like SEARCH, ISNUMBER, and FILTER to achieve partial keyword matching without needing to open a separate query editor.
Frequently Asked Questions
Can I make the Power Query keyword filter case-insensitive?
Yes, when using the Text.Contains function in Power Query, you can add a third parameter: Comparer.OrdinalIgnoreCase. This forces the query to ignore capitalization when comparing your data against your keyword list.
Why is my fuzzy join returning incorrect matches?
Fuzzy matching relies on a similarity threshold between 0.00 and 1.00. If the threshold is set too low (e.g., below 0.60), the algorithm becomes too lenient and will match words that only share a few letters. Increase the threshold in the Fuzzy matching options to improve accuracy.
Can I filter partial text using standard formulas instead of Power Query?
Yes. In modern spreadsheet applications, you can use a combination of the FILTER, ISNUMBER, and SEARCH functions to extract rows based on partial text matches directly on the spreadsheet canvas, without relying on Power Query.
How do I update the keyword list after setting up the query?
Because the keyword list is loaded from an Excel Table, you simply need to add or remove keywords directly in your Excel sheet, and then click 'Refresh All' on the Data tab. Power Query will automatically re-evaluate the text rows using the updated list.
