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| 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.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 falseHere, 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 trueAs the article shows, understanding the Text.EndsWith function and its options help improve your ability to handle text data in Power Query M.
Related articles
Learn more about Text.EndsWith 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
Related functions
Other functions related to Text.EndsWith are:
