Text.AfterDelimiter

What is Power Query M?

Power Query M is a functional language that is used to query and transform data in Power BI. It is a powerful tool that allows users to manipulate data in a variety of ways, including merging data, filtering data, and pivoting data. The language is designed to be easy to learn and can be used by users with little to no programming experience.

Understanding the Text.AfterDelimiter Function

The Text.AfterDelimiter function is designed to extract text that appears after a specific delimiter. The delimiter is defined as the character or sequence of characters that separates the text to be extracted from the rest of the text string.

The syntax for the Text.AfterDelimiter function is as follows:

Where:

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

– [Delimiter]: The character or sequence of characters that separates the text to be extracted from the rest of the text string.

– [Occurrence]: (Optional) The occurrence of the delimiter to use when extracting the text. The default value is 1.

The Text.AfterDelimiter function returns the text that appears after the specified delimiter. If the delimiter is not found, the function returns a null value. If the occurrence of the delimiter is not found, the function returns an error.

Examples of Using the Text.AfterDelimiter Function

Let’s take a look at some examples of how the Text.AfterDelimiter function can be used to extract text from a variety of text strings.

Example 1: Extracting Text After a Single Character Delimiter

Suppose we have a text string that contains a person’s full name, with the first and last names separated by a comma. We can use the Text.AfterDelimiter function to extract the last name from the text string.

In this example, the Text.AfterDelimiter function is used to extract the last name from the text string “Smith, John”. The delimiter is defined as a comma, and the occurrence is set to 1. The function returns the text ” John”.

Example 2: Extracting Text After a Multi-Character Delimiter

Suppose we have a text string that contains a product code, with the code and description separated by a hyphen. We can use the Text.AfterDelimiter function to extract the description from the text string.

In this example, the Text.AfterDelimiter function is used to extract the description from the text string “12345-Widget”. The delimiter is defined as a hyphen, and the occurrence is set to 1. The function returns the text “Widget”.

Example 3: Extracting Text After a Delimiter That Appears Multiple Times

Suppose we have a text string that contains a URL, with the domain name separated from the rest of the URL by two forward slashes. We can use the Text.AfterDelimiter function to extract the domain name from the text string.

In this example, the Text.AfterDelimiter function is used to extract the domain name from the text string “https://www.example.com/path/to/page.html”. The delimiter is defined as two forward slashes, and the occurrence is set to 1. The function returns the text “www.example.com/path/to/page.html”.

The Text.AfterDelimiter function is a powerful tool in Power Query M that is used to extract text after a specific delimiter. This function is designed to work with any text string and can be used to perform a wide range of text manipulation tasks. By understanding the M code behind this function, users can extract the text they need from even the most complex text strings.

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

Upcoming Courses

Contact Us