Text.EndsWith

Updated on

Text.EndsWith is a Power Query M function that determines if a given text value ends with a specified substring. The function returns true if the text ends with the substring, with optional comparers to specify case-insensitive or culture-aware comparisons.

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

Syntax

Text.EndsWith(
   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.EndsWith is a function that verifies whether a piece of text ends with a particular substring. If it does, the function returns true; if not, it returns 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

Let’s explore the Text.EndsWith function in the Power Query M language. This function enables you to check whether a string ends with a specific substring. To understand its usage, we’ll examine several examples.

Let’s say you wish to confirm whether the phrase “Biking to home” ends with the word “home”. You can do this as follows:

Text.EndsWith( "Biking to home", "home" )   

The function respects letter casing by default. That means it considers “HOME” to be different from “home”. Let’s see what happens when we replace “home” with “HOME”:

Text.EndsWith( "Biking to home", "HOME" ) 

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

Text.EndsWith( "Biking to home", "HOME", Comparer.Ordinal ) 

By default, the function respects casing. When you omit the comparer argument, Text.EndsWith uses Comparer.Ordinal by default. And with a case-sensitive comparison “HOME” isn’t the same as “home”.

But what if you want the function to overlook letter casing and treat “HOME” and “home” as identical? This can be achieved using the Comparer.OrdinalIgnoreCase function. Here’s how:

Text.EndsWith( "Biking to home", "HOME", Comparer.OrdinalIgnoreCase )

There’s more to the Text.EndsWith function rather than just case sensitivity, though. It can also handle comparisons that are dependent on cultural conventions with the help of the Comparer.FromCultureargument. Check out these examples:

Text.EndsWith( "skæ", "kae", Comparer.FromCulture( "en-US" ) ) // Returns true

Text.EndsWith( "skæ", "kae", Comparer.FromCulture( "da-DK" ) ) // Returns false

Here, the characters “æ” and “ae” are treated as equivalent in the English language, while they’re seen as distinct in Danish.

Please note that even with cultural comparisons, casing still matters. You can alter this behaviour using the optional second argument of Comparer.FromCulture:

Text.EndsWith( "skæ", "KAE", Comparer.FromCulture( "en-US", false ) ) // Returns false

Text.EndsWith( "skæ", "KAE", Comparer.FromCulture( "en-US", true ) ) // Returns true

As the article shows, understanding the Text.EndsWith function and its options help improve your ability to handle text data in Power Query M.

Learn more about Text.EndsWith in the following articles:

Other functions related to Text.EndsWith are:

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