Text.BetweenDelimiters

Understanding the Text.BetweenDelimiters Function

Before we dive into the M code, it’s important to understand how the Text.BetweenDelimiters function works. The Text.BetweenDelimiters function takes three arguments:

1. Text: The text you want to extract from.

2. Left Delimiter: The left delimiter that marks the beginning of the string you want to extract.

3. Right Delimiter: The right delimiter that marks the end of the string you want to extract.

The Text.BetweenDelimiters function then returns the text that’s between the two specified delimiters. Let’s take a look at an example to understand this better:

Suppose you have the following text: “Hello World! This is a sample text.” You want to extract the text between the words “Hello” and “sample”. You can use the Text.BetweenDelimiters function as follows:

The above formula will return the following text: ” World! This is a “.

Now that we understand how the function works, let’s take a look at the M code that powers the Text.BetweenDelimiters function.

The M Code Behind the Text.BetweenDelimiters Function

The Text.BetweenDelimiters function is a built-in function in Power Query, which means its underlying M code is not visible to the user. However, we can use the “view native query” feature of Power Query to view the underlying M code.

To view the M code behind the Text.BetweenDelimiters function, we first need to create a sample query that uses the Text.BetweenDelimiters function. Let’s create a simple query that uses the Text.BetweenDelimiters function:

1. Open Power Query and create a new query.

2. In the “Home” tab, click on “From Other Sources” and select “Blank Query”.

3. In the “Query Editor” window, click on “View” and select “Advanced Editor”.

4. In the “Advanced Editor” window, copy and paste the following M code:

5. Click on “Done” to close the “Advanced Editor” window.

Now that we have a sample query that uses the Text.BetweenDelimiters function, we can view the M code by following these steps:

1. In the “Query Editor” window, click on “View” and select “View Native Query”.

2. A new window will open that displays the underlying M code for the query. The M code for our sample query looks like this:

As you can see, the M code for the Text.BetweenDelimiters function is quite simple. The function takes three arguments, which are passed in as parameters to the function. The M code simply calls the Text.BetweenDelimiters function with the specified parameters and returns the result.

The Text.BetweenDelimiters function is a powerful tool in the Power Query arsenal that helps you extract text between two specified delimiters. While the function is easy to use, it’s always useful to understand how it works behind the scenes. In this article, we took a deep dive into the M code behind the Text.BetweenDelimiters function and learned how it powers this useful function.

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

Upcoming Courses

Contact Us