Text.StartsWith

Updated on

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
ArgumentAttributeDescription
TextThe text you want to inspect.
SubstringThe substring you want to search for.
CompareroptionalUsed 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.

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”.

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”.

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.

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.

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:

Other functions related to Text.StartsWith are:

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