Power Query Problems

How to Tag Power Query Rows Using Keywords from Another Table

Maira Mehtab Oct 15, 2026 869 views

Question details

The user wants to automatically assign tags to text rows based on a separate keyword-to-tag reference table.

How to Tag Power Query Rows Using Keywords from Another Table

Product: Excel Power Query
Device & OS: not provided
Scenario: Categorizing or transforming data where untagged text entries need to be matched against a list of keywords, returning corresponding tags for single or multiple matches.
Observed behavior: The text rows require automated tagging. The goal is to append a new column that successfully identifies keywords within the text and outputs the mapped tags.

Before you start

Ensure both your main data table and the keyword-to-tag mapping table are formatted as Tables and loaded into the Power Query Editor before applying the custom formulas.

Solution 1Recommended

Use a Custom Column with List.Accumulate (For First Match)

Add a custom M formula in Power Query to iterate through the keyword table and return the first corresponding tag it finds in the text.

This method uses the List.Accumulate function to check each keyword in your mapping table against your main text column. If a match is found, it retrieves the associated tag based on the keyword's position.

Load your tables into Power Query

Go to Data > Get Data > From Table/Range to load both your main text table (Table A) and your keyword mapping table (Table B) into the Power Query Editor.

Add a Custom Column

Select your main table (Table A). In the Power Query ribbon, navigate to the Add Column tab and click on Custom Column.

Enter the List.Accumulate formula

In the Custom Column dialog, enter the following formula: =List.Accumulate(TableB[KEY],"",(state,current)=>if Text.Contains([TEXTCOLUMN],current) then TableB[TAG]{List.PositionOf(TableB[KEY],current)} else state). Ensure you replace 'TableB[KEY]', 'TableB[TAG]', and '[TEXTCOLUMN]' with your actual table and column names.

Apply and Load

Click OK to create the column. Review the assigned tags, then click Home > Close & Load to apply the changes to your spreadsheet.

Use a Custom Column with List.Accumulate (For First Match)

Case Sensitivity: The Text.Contains function is case-sensitive by default. To ignore case, convert both your text column and keyword list to lowercase before applying the formula.

Solution 2

Use Dynamic Array Formulas in the Excel Grid (For Multiple Matches)

If a row contains multiple keywords and you want all matching tags returned without using Power Query, use a dynamic array formula in the spreadsheet.

Tag Data Efficiently with WPS Spreadsheets

WPS Spreadsheets fully supports advanced dynamic array formulas like FILTER, SEARCH, and ARRAYTOTEXT. You can quickly tag complex text rows directly in the grid without needing to load heavy Power Query editors.

1. Open your dataset in WPS Spreadsheets: Launch WPS Office and open your workbook containing the raw text and the keyword mapping table.
2. Apply a dynamic array formula: Select the target cell and enter the FILTER and SEARCH combination formula to identify keywords and pull the corresponding tags.
3. Fill the series: Press Enter to instantly generate the comma-separated tags, then double-click the cell's bottom-right corner to automatically fill the rest of the rows.

Seamlessly compatible with Microsoft Excel (.xlsx) formats and formulasNative support for modern dynamic array functions for instant data taggingLightweight, fast performance for heavy data categorization tasksFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I return multiple tags if a row contains multiple keywords in Power Query?

Yes. Instead of using List.Accumulate which returns the first match or overwrites states, you can filter the keyword list for all matches and combine the corresponding tags using the Text.Combine function.

Why does my Power Query formula return an error or null?

This usually happens if the column names referenced in the formula do not exactly match the headers in your tables. Power Query's M language is case-sensitive, so ensure 'TableB[KEY]' and '[TEXTCOLUMN]' are typed exactly as they appear in the query editor.

How do I make the keyword matching case-insensitive?

By default, Power Query's Text.Contains is case-sensitive. You can make it case-insensitive by adding Comparer.OrdinalIgnoreCase as the third argument in the Text.Contains function.

Excel & Data Problems