Text.End

Introduction to Text.End

The Text.End function is used to extract a specified number of characters from the end of a text string. The function takes two arguments: the text string to extract characters from, and the number of characters to extract. The syntax for the function is as follows:

For example, suppose we have a column of email addresses in a table, and we want to extract the domain name from each address. We could use the Text.End function to extract the last three characters of each address, which would give us the domain name:

The M Code Behind Text.End

To understand the M code behind the Text.End function, let’s take a look at a simple example. Suppose we want to extract the last three characters of the text string “hello”. We can do this using the following M code:

The code starts with the “let” keyword, which is used to define variables. We define two variables: “text”, which contains the text string we want to extract characters from, and “count”, which contains the number of characters we want to extract.

Next, we use the Text.End function to extract the last three characters of the “text” variable. The result is stored in a new variable called “result”.

Finally, we use the “in” keyword to return the “result” variable. This is the output of our function.

Using Text.End in Power Query

Now that we understand the M code behind the Text.End function, let’s take a look at how we can use it in Power Query.

Suppose we have a table with a column of text strings, and we want to extract the last three characters of each string. We can do this using the following steps:

1. Select the column we want to extract characters from.

2. Click on the “Add Column” tab in the ribbon, and select “Custom Column”.

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

4. In the “Custom Column Formula” box, enter the following code:

Replace “Column1” with the name of the column we want to extract characters from.

5. Click “OK” to create the new column.

The result will be a new column containing the last three characters of each text string in the original column.

The Text.End function is a powerful tool for extracting characters from the end of a text string. By understanding the M code behind the function, we can create custom functions to perform complex data transformations in Power Query. Whether you’re new to Power Query or an experienced user, the Text.End function is a valuable addition to your toolkit.

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

Upcoming Courses

Contact Us