Text.Middle

Overview of the Text.Middle Function

The Text.Middle function is used to extract a specific number of characters from the middle of a text string. It takes three arguments:

– Text: The text string from which to extract the characters.

– Start: The position of the first character to extract.

– Number of characters: The number of characters to extract.

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

For example, if we have a text string “Lorem ipsum dolor sit amet”, and we want to extract 5 characters from the middle starting at position 7, we would use the following formula:

This would return the text string “ipsum”.

Understanding the M Code Behind Text.Middle

The Text.Middle function is a built-in function in Power Query that is written in the M language. The M language is a functional programming language that is used to create custom functions and manipulate data in Power Query. Understanding the M code behind the Text.Middle function can help users to create more complex data transformations and custom functions.

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

Let’s break down this code into its individual components:

– The first line defines the function signature. It takes three arguments: Text, Start, and Count.

– The “let” statement defines two variables: StartIndex and EndIndex. StartIndex is calculated by subtracting 1 from the Start argument, and adding the Count argument. EndIndex is calculated by adding the Count argument to the StartIndex.

– The “in” statement defines the output of the function. It checks to see if the StartIndex and EndIndex are within the bounds of the Text string, and returns null if they are not. If they are within bounds, it uses the Text.Range function to extract the desired characters from the Text string.

Using Text.Middle in Power Query

Now that we understand the M code behind the Text.Middle function, let’s explore some practical examples of how it can be used in Power Query.

Example 1: Extracting First and Last Names from a Full Name

Suppose we have a table of customer names in the format “First Last”. We want to split this into separate columns for the first and last name. We can use the Text.Middle function to extract the first and last name from the full name. Here’s how we can do this:

1. Load the table into Power Query.

2. Add a custom column with the formula `= Text.PositionOf([Full Name], ” “)`. This will give us the position of the space between the first and last name.

3. Add two more custom columns with the formulas:

– `= Text.Middle([Full Name], 1, [Space Position] – 1)`. This will extract the first name.

– `= Text.Middle([Full Name], [Space Position] + 1, Text.Length([Full Name]) – [Space Position])`. This will extract the last name.

Example 2: Extracting Numbers from a Text String

Suppose we have a column of text strings that contain numbers in the format “Number: Value”. We want to extract the number from each string. We can use the Text.Middle function to extract the number from each string. Here’s how we can do this:

1. Load the table into Power Query.

2. Add a custom column with the formula `= Text.PositionOf([Text String], “:”)`. This will give us the position of the colon between the number and the value.

3. Add a second custom column with the formula `= Text.Middle([Text String], 1, [Colon Position] – 1)`. This will extract the number from the text string.

The Text.Middle function is a powerful tool for extracting a specific number of characters from the middle of a text string in Power Query. Understanding the M code behind the function can help users to create more complex data transformations and custom functions. By using Text.Middle in combination with other Power Query functions and techniques, users can manipulate and shape data in powerful and flexible ways.

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

Upcoming Courses

Contact Us