Text.Remove

Updated on

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 text

Description

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"

Learn more about Text.Remove in the following articles:

Other functions related to Text.Remove are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-remove