Text.Remove is a Power Query M function that removes all occurrences of specified characters from a text value. The function returns the modified text value with the specified characters removed.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.Remove(
text as nullable text,
removeChars as any,
) as nullable textDescription
Returns a copy of the text value text with all the characters from removeChars removed.
Examples
The Text.Remove function is an invaluable tool when it comes to data cleansing. Consider a scenario where you’d like to remove a specific character, such as “o”, from a text string, “Rick de Groot”. This is achieved as follows:
Text.Remove( "Rick de Groot", "o" ) // returns "Rick de grt"
It’s important to note that the Text.Remove function is case sensitive. This results in different outcomes based on the casing of the input:
Text.Remove( "Rick", "R" ) // Returns "ick"
Text.Remove( "Rick", "r" ) // Returns "Rick" as is This highlights the fact that “R” and “r” are distinct characters as far as Text.Remove function is concerned.
If you need to remove multiple characters, Text.Remove allows you to provide a list of characters to be eliminated.
// Below example returns "Rick"
Text.Remove(
"Rick de Groot",
{ " ", "d", "e", "G", "r", "o", "t" }
)The function is versatile in accepting different types of characters, including numbers, letters, and special characters, as long as they are provided in a text format. Here’s an example:
Text.Remove( "A/B_C", { "/", "_" } ) // Returns "ABC"
An interesting way to input values is the utilization of a shortcut to generate the values to be removed, known as the two dot operators. More details about how to generate lists of values can be found in this article. Here are some examples:
Text.Remove( "power-BI99", { "A".."Z" } ) // "power-99"
Text.Remove( "power-BI99", { "a".."z" } ) // "-BI99"
Text.Remove( "power-BI99", { "a".."z", "A".."Z" } ) // "-99"
Text.Remove( "power-BI99", { "0".."9" } ) // "power-BI"Related articles
Learn more about Text.Remove in the following articles:
- Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Related functions
Other functions related to Text.Remove are:
