Text.Select

What is Text.Select?

The Text.Select function is used to extract a substring from a text value. It takes two arguments, the text value and a range. The range specifies the starting position and the length of the substring to extract. The Text.Select function is commonly used in data cleaning tasks where we need to extract specific parts of a text value.

M Code Behind Text.Select

The M code behind the Text.Select function is straightforward. Let’s take a look at an example:

The Text.Select function takes three parameters, Text, Start, and Count. The first parameter ‘Text’ is the input text value. The second parameter ‘Start’ is the starting position of the substring to extract. The third parameter ‘Count’ is the length of the substring to extract.

The Text.Select function starts with a ‘let’ statement, which creates a new variable called ‘SubText.’ The ‘if’ statement checks if the starting position is greater than zero. If it is, then the ‘SubText’ variable is set to the input text value. Otherwise, it is set to an empty string.

The next ‘let’ statement creates another variable called ‘SubText2.’ The ‘if’ statement checks if the length of the substring to extract is greater than zero. If it is, then the ‘SubText2’ variable is set to the substring extracted using the Text.Range function. Otherwise, it is set to an empty string.

Finally, the Text.Select function returns the ‘SubText2’ variable, which contains the extracted substring.

Examples

Let’s take a look at some examples of how to use the Text.Select function in Power Query.

Example 1: Extracting First N Characters

Suppose we have a column called ‘Product Name’ in our data, which contains the names of various products. We want to extract the first five characters of each product name. Here’s how we can do it using the Text.Select function:

1. Select the ‘Product Name’ column and click on the ‘Add Column’ tab.

2. Click on the ‘Custom Column’ button to open the ‘Add Custom Column’ dialog box.

3. In the ‘New column name’ field, enter a name for the new column, e.g., ‘First 5 Characters.’

4. In the ‘Custom column formula’ field, enter the following formula:

5. Click on the ‘OK’ button to create the new column.

The Text.Select function extracts the first five characters of each product name and creates a new column called ‘First 5 Characters.’

Example 2: Extracting Last N Characters

Suppose we have a column called ‘Email’ in our data, which contains email addresses. We want to extract the last two characters of each email address. Here’s how we can do it using the Text.Select function:

1. Select the ‘Email’ column and click on the ‘Add Column’ tab.

2. Click on the ‘Custom Column’ button to open the ‘Add Custom Column’ dialog box.

3. In the ‘New column name’ field, enter a name for the new column, e.g., ‘Last 2 Characters.’

4. In the ‘Custom column formula’ field, enter the following formula:

5. Click on the ‘OK’ button to create the new column.

The Text.Select function extracts the last two characters of each email address and creates a new column called ‘Last 2 Characters.’

The Text.Select function is a powerful function in Power Query that helps in extracting substrings from text values. By understanding the M code behind the Text.Select function, we can create more complex and customized transformations in our data.

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

Upcoming Courses

Contact Us