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"
Related articles
Learn more about Text.RemoveRange 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.RemoveRange are: