Power Query Problems

How to Simplify Power Query Text.Contains Rules with a Reference Table

John Wilson Oct 9, 2026 868 views

Question details

The user wants to replace long, nested Text.Contains statements with a dynamic word-to-category reference table in Power Query to make categorizing bank transactions easier to maintain.

How to Simplify Power Query Text.Contains Rules with a Reference Table

Product: Excel Power Query
Device & OS: not provided
Scenario: Categorizing bank transactions using partial text matching without having to manually edit M code every time a new search term is added.
Observed behavior: Long nested Text.Contains rules become too complex to maintain, and exact match lists fail to identify partial text strings like MCD within POS/MCDONALD'S.

Before you start

Ensure you have your raw bank transactions and a separate, prepared Reference Table (containing a 'SearchWord' column and a 'Category' column) loaded into Power Query.

Solution 1Recommended

Create a Custom Power Query Function for Partial Matches

Build a reusable custom M function that scans your transaction descriptions against a reference table and returns the matched category.

By utilizing a custom function, you can keep your M code clean and delegate the ongoing maintenance of search terms entirely to a standard Excel table. When team members update the reference table, the query automatically applies the new rules upon refresh.

Format Your Reference Table

In your spreadsheet, create a table with two columns: 'SearchTerm' (e.g., MCD) and 'Category' (e.g., Dining). Name this table 'ReferenceTable' and load it into Power Query.

Create a Blank Query for the Custom Function

In the Power Query Editor, right-click in the Queries pane, select 'New Query', and choose 'Blank Query'. Open the Advanced Editor from the Home tab.

Input the Custom M Code

Paste a custom function that takes a text input, filters the ReferenceTable using Text.Contains, and returns the Category. Name this query 'fxGetCategory'.

Invoke the Custom Function

Go to your main Transactions query. Click 'Add Column' > 'Invoke Custom Column'. Select 'fxGetCategory' and pass your transaction description column as the argument.

Handle Fallbacks and Expand

If the function returns an empty table (no match), replace the null value with an 'Uncategorized' fallback. Expand the resulting column to reveal your final categories.

Create a Custom Power Query Function for Partial Matches

Case Sensitivity: To prevent missed matches due to capitalization variations, wrap your text strings in Text.Upper() or use Comparer.OrdinalIgnoreCase in your Text.Contains logic.

Solution 2

Use List.Accumulate for Dynamic Replacement

An alternative M code solution that iterates over a list of search terms and replaces text or assigns categories iteratively.

Free Microsoft Office alternative

Looking for a Simpler Way to Manage Spreadsheets?

If writing complex Power Query M code feels frustrating, try WPS Office. It offers a lightweight, easy-to-use interface with powerful built-in formulas (like XLOOKUP and SEARCH arrays) that can categorize data easily without advanced programming skills.

1. Download and Install: Get WPS Office Free from the official website and install it on your device in minutes.
2. Open Your Files Seamlessly: Open your existing .xlsx workbooks directly in WPS Spreadsheet with zero format loss.
3. Use Built-in Formulas: Utilize powerful array formulas like LOOKUP and SEARCH combinations to match partial text instantly without writing a single line of M code.

100% compatible with Microsoft Excel (.xlsx) formatsLightweight design ensures fast loading and smooth processing of large datasetsFamiliar user-friendly interface makes transitioning from MS Office seamlessPowerful built-in formulas for data categorization without complex M code

microsoft office alternative - wps office

Frequently Asked Questions

Why is nested Text.Contains difficult to maintain?

Nested Text.Contains statements require complex if/then/else logic in M code. Every time you add a new search term, you must manually open the Advanced Editor and alter the code, increasing the risk of syntax errors.

Can standard users update the reference table without opening Power Query?

Yes. Once the custom function is set up, standard users can simply add or remove rows in the Excel reference table. Refreshing the data connections will automatically apply the new terms.

Is Power Query partial text matching case-sensitive?

Yes, Power Query's Text.Contains function is case-sensitive by default. It will view 'mcd' and 'MCD' as completely different strings unless you explicitly specify an ignore-case comparer.

Excel & Data Problems