How to Remove Text After a Specific Word in Power Query
Question details
The user needs to extract text from a string up to the word "NUL" and discard the remaining characters while keeping "NUL" in the final result.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Cleaning and formatting data strings in Power Query.
- Observed behavior
- The goal is to truncate strings after a specific substring, for example, converting "1287JLPO-NUL-00-000-00000" to "1287JLPO-NUL".
Ensure your dataset is loaded into the Power Query Editor and identify the exact column containing the strings you want to modify. Note that Power Query is case-sensitive, so confirm the exact capitalization of your target word.
Use Text.BeforeDelimiter and Append Text in M Code
This is the most efficient and direct method using Power Query's custom column formula.
The standard extraction tool removes the delimiter itself. By using M code, you can extract the text before the delimiter and immediately concatenate the delimiter back onto the result string.
In the Power Query Editor, navigate to the Add Column tab on the top ribbon and click on Custom Column.
In the Custom Column dialog, enter the following formula: = Table.AddColumn(Source, "Before delimiter", each Text.BeforeDelimiter([String], "NUL") & "NUL"). Ensure you replace [String] with your actual column name.
Click OK to save the column. The new column will display the truncated text while retaining the "NUL" string. You can now load the cleaned data.
Use Replace Values and Text Extraction (No-Code Alternative)
A no-code alternative using the Power Query user interface to replace values and extract text.
Looking for a Free and Powerful Spreadsheet Editor?
While Power Query is a feature specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for your daily spreadsheet and data processing needs. With advanced formulas, intuitive data cleaning tools, and familiar interfaces, you can seamlessly manage your data without hefty subscription fees.
- 1. Download and Install: Visit the WPS Official website to download the free WPS Office suite.
- 2. Open Your Data File: Launch WPS Spreadsheets and open your .xlsx or .csv files seamlessly.
- 3. Use Text Formulas: Utilize built-in text functions like LEFT and SEARCH to clean and extract your data quickly without needing complex scripting languages.
Frequently Asked Questions
Does Power Query's Text.BeforeDelimiter keep the delimiter itself?
No, by default, the Text.BeforeDelimiter function removes the specified delimiter along with the trailing text. To retain the delimiter, you must concatenate it back to the result using the ampersand (&) operator.
Is Power Query M code case-sensitive when searching for text?
Yes, M code is strictly case-sensitive. Searching for "NUL" will not match "nul" or "Nul". Ensure your delimiter matches the exact capitalization in your dataset.
How can I accomplish this using standard Excel or WPS Spreadsheets formulas instead of Power Query?
You can use a combination of standard spreadsheet functions. Assuming your text is in cell A1, you can use the formula =LEFT(A1, SEARCH("NUL", A1) + 2) to extract the text and keep "NUL".
