Text.startswith power query secrets & use cases

The “Text.startswith power query” function helps us to find the substring in Power BI. It is pretty simple & easy to use.

Below you can see the syntax.

Text.StartsWith(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

You usually need two parameters to work with text.startswith function.

• The first parameter is the column name in which you need to find the substring
• The second parameter is the substring you are looking for in the first parameter.

Text.startswith Power Query Example

Let’s take the following example to further elaborate text.startswith function.

We have a customer table, and we want to find customers with a first name starting with “Fr.”

You need to use the following steps to implement it.

1. Open the power query editor
2. Select add column
3. Select custom column

Text.StartsWith([Name],"Fr")

This function will return true if the customer’s first name starts with “Fr.”

Let’s filter the “Filtered customer” to true, and you will see the following result.

Text.startswith is a case-sensitive function. It means you should be very careful while passing the substring.

Let’s try using the following function and see if you have any true value.

Text.StartsWith([Name],"FR")

It will return “False” because no customer name is available that starts with capital FR.

How to return actual values instead of true/false

Now we need to find out those customers where the first name starts with “Fr” instead of returning true and false.

We must use the if else condition in the power query to accomplish this requirement.

if Text.StartsWith([Name],"Fr") 
   then [Name]
else null

It says if the function returns true, we display the customer’s name; otherwise null.

It says if the function returns true, we display the customer’s name; otherwise null.
For the output of the above expression, you can see below.

Power query text.startswith multiple values

We can use text.starswith function with multiple values. Let’s dive into it a bit more.

We will take the same example again but with multiple values. 

We need to find those customers where the first name starts with “Fr,” whose salary is greater than 3000, and whose address begins with “19.”

if Text.StartsWith([Name],"Fr") 
and Text.StartsWith([AddressLine1],"19") 
and [YearlyIncome] > 3000
then [Name] else null

In this blog post, you have learned how to use text.startswith function in power query.

For more useful blogs, please visit Learn DAX