Text.StartsWith

What is Power Query?

Power Query is a data transformation and cleansing tool that allows users to extract, transform, and load data from various sources. It is a part of Microsoft’s Power BI suite of products and is also available as an add-in for Excel. Power Query uses a functional language called M to perform data transformations.

Understanding the `Text.StartsWith` function

The `Text.StartsWith` function is used to check if a text value starts with a specific substring. The function takes two arguments: the text value to check and the substring to search for. If the text value starts with the specified substring, the function returns `True`. Otherwise, it returns `False`.

The syntax for the `Text.StartsWith` function is as follows:

Here, `text` is the text value to check, and `substring` is the substring to search for.

The M Code Behind `Text.StartsWith`

The M code behind the `Text.StartsWith` function is quite simple. In fact, it is just a single line of code. Here is the M code for the `Text.StartsWith` function:

The M code defines an anonymous function that takes two arguments (`text` and `substring`) and returns a logical value (`True` or `False`). The function uses two other M functions: `Text.Start` and `Text.Length`.

The `Text.Start` function takes two arguments: the text value to extract a substring from, and the number of characters to extract. In this case, we want to extract the first `n` characters from the text value, where `n` is the length of the substring we are searching for.

The `Text.Length` function takes a single argument (the substring to search for) and returns the length of the substring.

Finally, the anonymous function uses the `=` operator to compare the extracted substring to the substring we are searching for. If they are equal, the function returns `True`. Otherwise, it returns `False`.

Using `Text.StartsWith` in Power Query

Now that we understand the M code behind the `Text.StartsWith` function, let’s see how we can use it in Power Query.

Suppose we have a table of customer data with a column called `Name`. We want to create a new column called `Is VIP` that indicates whether a customer’s name starts with the letters “VIP”. We can use the `Text.StartsWith` function to do this.

First, we select the `Name` column and click the “Add Column” tab. Then, we click the “Custom Column” button and enter the following M code:

This code creates a new column that checks whether the value in the `Name` column starts with the letters “VIP”. The result is either `True` or `False`, depending on whether the condition is met.

The `Text.StartsWith` function is a powerful tool for working with text data in Power Query. By understanding the M code behind the function, we can use it to create custom columns and perform complex data transformations. Whether you are new to Power Query or an experienced user, the `Text.StartsWith` function is a valuable addition to your toolbox.

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

Upcoming Courses

Contact Us