Text.RemoveRange

Updated on

Text.RemoveRange is a Power Query M function that removes a specified number of characters from a text value starting at a specified position. The function returns the modified text value with the characters removed, with an optional count parameter to customize the removal.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.RemoveRange(
   text as nullable text,
   offset as number,
   optional count as nullable number,
) as nullable text

Description

Returns a copy of the text value text with all the characters from position offset removed. An optional parameter, count can by used to specify the number of characters to remove. The default value of count is 1. Position values start at 0.

Examples

Let’s further explore the Text.RemoveRange function in Power Query M language using a series of examples. Text.RemoveRange serves as a useful tool for eliminating specified portions of text values based on their position and length.

Let’s start by removing a single character from the text value “ABEFC” at position 2. If we don’t tell Text.RemoveRange how many characters to remove (the optional third argument), it will just remove one. The position indexing in Power Query M language begins at 0, so position 2 refers to the third character in the string:

Text.RemoveRange( "ABEFC", 2 )

In other instances, you might need to remove multiple characters from the start of a string. With Text.RemoveRange, this task becomes quite simple. For instance, let’s strip the first five characters from the text value “9-AR-C-85”:

Text.RemoveRange( "9-AR-C-85", 0, 5 ) // Returns "C-85"

Moreover, Text.RemoveRange function allows for precise character removal from any specified position in a string. Suppose we want to start from position 3 (fourth character) and remove the next three characters in the string “9-AR-C-85”:

Text.RemoveRange( "9-AR-C-85", 3, 3 ) // Returns "9-A-85"

The Text.RemoveRange function in Power Query M language enables targeted removal of character sets within a string, not just single characters. By specifying the length of characters to be removed from a particular position, you can achieve high precision in text transformation. Consider removing two characters from “ABEFC” starting at position 2:

Text.RemoveRange( "ABEFC", 2, 2 ) // Returns "ABC"

Learn more about Text.RemoveRange in the following articles:

Other functions related to Text.RemoveRange are:

BI Gorilla Youtube Channel

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