Text.Remove

D

Back to top

T

Back to top

#

Back to top

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

What is the Text.Remove Function?

The Text.Remove function is a Power Query M function that is used to remove a specified number of characters from a given text string. It takes two arguments: the text string to be modified and the number of characters to remove. Here is the syntax of the Text.Remove function:

Text.Remove(text as nullable text, count as number) as nullable text

The first argument, “text”, is the text string that you want to modify. The second argument, “count”, is the number of characters that you want to remove from the beginning of the text string. If the second argument is negative, the Text.Remove function will remove characters from the end of the text string.

How to Use the Text.Remove Function

Using the Text.Remove function is simple. First, you need to create a query in Power Query. Once you have your query, you can add the Text.Remove function to the query by following these steps:

1. Select the column that you want to modify in the query editor

2. Click on the “Add Column” tab in the ribbon

3. Click on the “Text” dropdown menu

4. Select the “Remove” option

5. Enter the number of characters to remove in the “Number of Characters” field

Here is an example of how to use the Text.Remove function to remove the first three characters from a text string:

= Table.AddColumn(#"Previous Step", "New Column", each Text.Remove([Old Column], 3))

This formula will create a new column called “New Column” that contains the original text string with the first three characters removed.

The M Code Behind the Text.Remove Function

Under the hood, the Text.Remove function is actually a combination of two other M functions: Text.Start and Text.Length. Here is the M code for the Text.Remove function:

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

let

length = Text.Length(text),

start = if count < 0 then length + count else count,

result = if start < length then Text.Start(text, start) else ""

in

result

The first line of the code defines the two arguments of the function: “text” and “count”. The second line calculates the length of the text string using the Text.Length function. The third line calculates the starting position of the characters to be removed based on the value of “count”. If “count” is negative, it is added to the length of the text string to get the starting position. If “count” is positive, it is used as the starting position.

The fourth line uses the Text.Start function to extract the characters from the text string starting from the calculated starting position. The fifth line checks if the starting position is less than the length of the text string. If it is, the extracted characters are returned. If it is not, an empty string is returned.

The Text.Remove function is a useful tool for cleaning and transforming data in Power Query. Its M code is relatively simple, but understanding how it works can help you use it more effectively. By using the Text.Remove function in combination with other M functions, you can create complex data transformations that meet your specific needs.

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)