Text.Remove - Text Function

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

The Text.Remove function takes a text value and removes all the characters specified in the removeChars argument.

Examples

Let’s explore how to use it with various examples.

Removing a Specific Character

Consider a scenario where you’d like to remove a specific character, such as “o”, from the text string “Rick de Groot”. This can be achieved as follows:

Text.Remove( "Rick de Groot", "o" ) // returns "Rick de grt"

Case Sensitivity

It’s important to note that the Text.Remove function is case sensitive, resulting 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 indicates that “R” and “r” are distinct characters as far as Text.Remove function is concerned.

Removing Multiple Characters

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" } 
)

Removing Special Characters

The function is versatile and accepts 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"

Using Ranges to Remove Characters

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:

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

Other functions related to Text.Remove are:

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