Text.BeforeDelimiter

The M code behind the Text.BeforeDelimiter function is what makes it possible to extract text from a string. The M language is a functional language used in Power Query to transform data. It is a powerful language that allows users to create complex transformations.

Understanding the Text.BeforeDelimiter Function

The Text.BeforeDelimiter function is used to extract text from a string before a delimiter character. For example, if we have a string “John Smith,CEO” and we want to extract the text before the comma, we can use the Text.BeforeDelimiter function. The function takes two arguments, the string and the delimiter character.

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

Text.BeforeDelimiter(string as text, delimiter as text) as text

The function returns the text before the delimiter character. If the delimiter character is not found in the string, the function returns a blank value.

The M Code Behind Text.BeforeDelimiter

The M code behind the Text.BeforeDelimiter function is fairly simple. The function uses the Text.PositionOf function to find the position of the delimiter character in the string. It then uses the Text.Start function to extract the text before the delimiter character.

The M code for the Text.BeforeDelimiter function is as follows:

In the code above, we can see that the function takes two parameters, Text and Delimiter. The Text.PositionOf function finds the position of the Delimiter character in the Text string. If the Delimiter character is not found, the Text.PositionOf function returns -1. The function then uses the Text.Start function to extract the text before the delimiter character. If the delimiter character is not found in the string, the function returns the entire string.

Examples of Text.BeforeDelimiter in Use

Let’s take a look at some examples of how the Text.BeforeDelimiter function can be used in Power Query.

Example 1: Extracting First Name from Full Name

Suppose we have a table with a column Full Name that contains the full names of employees. We want to extract the first name of each employee from the Full Name column.

To do this, we can use the Text.BeforeDelimiter function to extract the first name before the space character. The M code for this transformation is as follows:

In the code above, we are adding a new column to the table called First Name. The Text.BeforeDelimiter function is used to extract the first name from the Full Name column.

Example 2: Extracting Domain Name from Email Address

Suppose we have a table with a column Email Address that contains the email addresses of customers. We want to extract the domain name from each email address.

To do this, we can use the Text.BeforeDelimiter function to extract the domain name after the @ character. The M code for this transformation is as follows:

In the code above, we are adding a new column to the table called Domain Name. The Text.AfterDelimiter function is used to extract the text after the @ character. The Text.BeforeDelimiter function is then used to extract the domain name before the . character.

The Text.BeforeDelimiter function is a powerful tool in Power Query that allows users to extract text from a string before a delimiter character. The M code behind the function is fairly simple and can be easily understood. With the help of examples, we have seen how the Text.BeforeDelimiter function can be used to extract first names from full names and domain names from email addresses.

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

Upcoming Courses

Contact Us