How to Remove Specific Words from a Column in Power Query
Question details
The user needs to remove specific complete words (such as BLACK, WHITE, RED) from a text column in Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning a dataset by removing multiple specific whole words from a column's text values.
- Observed behavior
- The native Text.Remove function deletes individual characters rather than complete words, requiring an alternative approach using text replacement lists.
Before modifying your query, identify the exact spelling and case of the words you wish to remove, as Power Query M code is strictly case-sensitive.
Use List.Accumulate and Text.Replace in the Advanced Editor
The most efficient and scalable way to remove multiple specific words from a column is by iterating a list of target words using List.Accumulate combined with Text.Replace.
Since the Text.Remove function targets individual characters, removing whole strings requires a replacement operation. By nesting Text.Replace inside List.Accumulate, Power Query will systematically check for and replace every word in your defined list with an empty string.
Select your data table in Excel, navigate to the Data tab, and click 'From Table/Range' to launch the Power Query Editor.
With your query selected, go to the Home tab and click on 'Advanced Editor' to view the M code.
Add a new step with the following formula to replace the specific words. Ensure you modify 'Source' to match your previous step name and 'DESCRIPTION' to match your column name: =Table.ReplaceValue(Source,each [DESCRIPTION],"",(x,y,z)=>List.Accumulate({"BLACK","Black","WHITE","White","RED","Red"},x,(accum,item)=>Text.Replace(accum,item,z)),{"DESCRIPTION"})
Click 'Done' to close the Advanced Editor, verify that the words have been removed from your column, and then click 'Close & Load' to output the cleaned data back into Excel.
Use Sequential Text.Replace Steps
If you only have one or two specific words to remove, chaining multiple Text.Replace functions is a simpler alternative to writing advanced M code.
Looking for a Lightweight, Free Microsoft Office Alternative?
While Power Query offers advanced data manipulation in Microsoft Excel, many users simply need straightforward spreadsheet tools without the heavy subscription fees. WPS Office Spreadsheets provides powerful built-in data cleaning, seamless format compatibility, and a familiar user interface entirely for free.
- 1. Download WPS Office: Install the free WPS Office suite on your computer or mobile device.
- 2. Open Your Spreadsheet: Open your existing Excel workbooks seamlessly without losing any of your data formatting.
- 3. Clean Your Data: Use the intuitive built-in Data tools and text manipulation functions to easily clean and organize your spreadsheet.
Frequently Asked Questions
Why does Text.Remove delete individual letters instead of the whole word?
In Power Query, the Text.Remove function is specifically designed to strip out individual characters from a text string. To remove complete words, you must use Text.Replace to swap the entire word string with an empty space.
How do I handle case sensitivity when removing words in Power Query?
Power Query is strictly case-sensitive, meaning 'Black' and 'BLACK' are treated as two distinct text values. You can either include all case variations in your replacement list or use Text.Upper or Text.Lower to standardize the text casing of the column before removing the words.
Can I use an external list of words instead of typing them into the formula?
Yes. You can create a separate Excel table containing the words you want to remove, import it into Power Query as a list, and reference that list's name within your List.Accumulate formula. This makes your query dynamic and easier to update without modifying the M code directly.
