Text.ReplaceRange

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.ReplaceRange

How Does `Text.ReplaceRange` Work?

The `Text.ReplaceRange` function takes three arguments: the text string, the starting index of the range to replace, and the number of characters to replace. Here is the syntax of the function:

Text.ReplaceRange(text as nullable text, offset as number, count as number, newtext as text) as nullable text

Let’s say we have the following text string:

"This is a test string"

If we want to replace the word “test” with “sample”, we can use the `Text.ReplaceRange` function like this:

Text.ReplaceRange("This is a test string", 10, 4, "sample")

In this case, the starting index of the range we want to replace is 10 (which is the index of the first character of the word “test” in the text string), and we want to replace 4 characters (which is the length of the word “test”). The result of this function call would be:

"This is a sample string"

The M Code Behind `Text.ReplaceRange`

The M code behind `Text.ReplaceRange` is fairly straightforward. Here is the code:

(text as nullable text, offset as number, count as number, newtext as text) =>

let

start = Text.Start(text, offset - 1),

end = Text.End(text, offset + count),

result = start & newtext & end

in

if text = null then null else result

Let’s break down this code and see how it works.

Arguments

The first line of the code defines the arguments of the function:

(text as nullable text, offset as number, count as number, newtext as text) =>

The `text` argument is the text string that we want to modify. It is defined as a nullable text, which means that it can be either a string or null.

The `offset` argument is the starting index of the range that we want to replace.

The `count` argument is the number of characters in the range that we want to replace.

The `newtext` argument is the text string that we want to replace the range with.

Variables

The next two lines define two variables, `start` and `end`, which represent the beginning and end of the text string before and after the range we want to replace:

start = Text.Start(text, offset - 1),

end = Text.End(text, offset + count),

The `Text.Start` function returns the first `offset – 1` characters of the text string, while the `Text.End` function returns the characters from `offset + count` to the end of the text string.

Result

The next line concatenates the values of the `start`, `newtext`, and `end` variables using the `&` operator:

result = start & newtext & end

This creates the modified text string.

Error Handling

Finally, the code includes an `if` statement to handle null values. If the `text` argument is null, the function returns null:

if text = null then null else result

In conclusion, the `Text.ReplaceRange` function in Power Query allows you to replace a specific range of characters within a text string. The M code behind this function is fairly simple and can be modified to suit your specific needs. Keep in mind that this function may not be the best choice for large data sets, as it can be relatively slow compared to other Power Query functions.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)