Text.RemoveRange

D

Back to top

T

Back to top

#

Back to top

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

What is the Text.RemoveRange function?

The Text.RemoveRange function is a Power Query M function that removes a specified number of characters from a text string, starting from a specified position. It takes two arguments: the text string and the number of characters to remove.

The syntax of the Text.RemoveRange function is as follows:

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

The “text” argument is the text string from which characters are to be removed. The “offset” argument is the position from which the characters are to be removed. The “count” argument is the number of characters to be removed.

How to use the Text.RemoveRange function?

To use the Text.RemoveRange function, follow the steps below:

1. Load the data into Power Query.

2. Select the column that contains the text string.

3. Click on the “Add Column” tab.

4. Click on “Custom Column”.

5. In the “Custom Column” dialog box, enter a name for the new column.

6. In the “Custom Column” dialog box, enter the following formula:

Text.RemoveRange([column name], [offset], [count])

Replace “[column name]” with the name of the column that contains the text string, “[offset]” with the position from which the characters are to be removed, and “[count]” with the number of characters to be removed.

7. Click on “OK”. The new column will be added to the table.

The M code behind the Text.RemoveRange function

The M code behind the Text.RemoveRange function is as follows:

(Text as nullable text, Offset as number, Count as number) =>

let

SourceLength = Text.Length(Text),

KeepCount = if (Offset < 0) then Count + Offset else Count,

KeepStart = if (Offset < 0) then 0 else Offset,

KeepEnd = SourceLength - KeepStart - KeepCount,

Result = if (KeepCount >= 0 and KeepEnd >= 0) then

Text.Start(Text, KeepStart) &

Text.End(Text, KeepEnd)

else

null

in

Result

The M code takes three parameters: “Text”, “Offset”, and “Count”. The code first gets the length of the text string using the Text.Length function. It then determines the number of characters to keep using the “Offset” and “Count” parameters. If the “Offset” is negative, it means that the characters are to be removed from the end of the string. In this case, the “KeepCount” variable is set to “Count + Offset”.

The code then calculates the starting position of the characters to keep using the “KeepStart” variable. It calculates the ending position of the characters to keep using the “KeepEnd” variable. The “Result” variable is then set to the characters to keep using the Text.Start and Text.End functions.

Finally, the code checks if the “KeepCount” and “KeepEnd” variables are not negative. If they are not negative, it returns the result. Otherwise, it returns null.

The Text.RemoveRange function is a useful feature of Power Query that allows users to remove characters from a text string based on a specified position and count. The M code behind the function is straightforward and easy to understand. By understanding the M code behind the Text.RemoveRange function, users can better utilize it to transform and clean their data.

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)