Text.StartsWith - Text Function

Text.StartsWith is a Power Query M function that checks if a text value begins with a specified substring, with an optional comparer for controlling the comparison. The function returns true if the text value starts with the substring, and false otherwise.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Text		The text you want to inspect.
Substring		The substring you want to search for.
Comparer	optional	Used to control how to compare values. When omitted, Power Query defaults to using Comparer.Ordinal for a case-sensitive comparison. To ignore capitalization when comparing values you can provide Comparer.OrdinalIgnoreCase. For a culture-aware comparison, you can make use of Comparer.FromCulture

Description

Text.StartsWith performs a simple yet useful task: it verifies whether a piece of text starts with a particular substring. If it does, the function will return true; if not, it’s false.

You can use the Comparer argument to control the comparison. Comparers can be used to provide case-(in)sensitive or culture-aware comparisons.

Examples

Now, let’s dive into some examples to see Text.StartsWith in action.Think of this function as your sleuth that can detect if a piece of text starts with a specific substring.

Checking the Start of a String

Imagine we’re trying to find if a song title begins with the phrase “I got”. Here’s how you’d do it:

Text.StartsWith( "I got the Power", "I got" ) // Returns true

Text.StartsWith tests for a substring in Power Query M

Our function returns true because indeed, “I got the Power” does start with “I got”.

Case Sensitivity

What happens when we switch the case of our substring? Let’s see:

Text.StartsWith( "I got the Power", "i got" )  // Returns false

/* -------- which is identical to -------- */

Text.StartsWith( "I got the Power", "i got", Comparer.Ordinal )  // Returns false

Text.StartsWith case sensitive testing in Power Query M

By default the function respects casing. When you omit the comparer argument, Text.StartWith uses Comparer.Ordinal by default. And with a case sensitive comparison “i got” isn’t the same as “I got”.

Ignoring Case Sensitivity

What if we want our function to ignore case? We’ve got the comparer argument to determine that. We can introduce Comparer.OrdinalIgnoreCase for case-insensitive comparisons:

Text.StartsWith( "I got the Power", "i got", Comparer.OrdinalIgnoreCase ) // Returns true

Text.StartsWith case insensitive testing in Power Query M

With Comparer.OrdinalIgnoreCase, our function now sees “i got” and “I got” as the same. Consequently it returns true.

Culture-Dependent Comparisons

But that’s not all. Our function can also handle culture-dependent comparisons using Comparer.FromCulture. Have a look at these:

Text.StartsWith( "ærefrygt", "aere", Comparer.FromCulture( "en-US" ) ) // Returns true

Text.StartsWith( "ærefrygt", "aere", Comparer.FromCulture( "da-DK" ) ) // Returns false

The characters “æ” and “ae” are considered identical in the English language, whereas in Danish these are different.

Case Sensitivity with Culture-Dependent Comparisons

Note that the comparison still respects casing when comparing values. You can change this behavior with the optional argument of Comparer.FromCulture.

Text.StartsWith( "ærefrygt", "AEre", Comparer.FromCulture( "en-US", false ) ) // Returns false

Text.StartsWith( "ærefrygt", "AEre", Comparer.FromCulture( "en-US", true) ) // Returns true

Understanding these nuances of the Text.StartsWith function helps you work with text data more effectively. Be it case-(in)sensitive data cleaning or simple substring checks, knowing how to use this function is useful in cleaning your Power Query data.

Learn more about Text.StartsWith in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.StartsWith are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-startswith