Text.EndsWith - Text Function

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

We’ll go through some examples to understand the usage of Text.EndsWith.

Checking if a String Ends with a Specific Substring

Suppose you want to check if the phrase “Biking to home” ends with the word “home”. You can do this as follows:

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

Case Sensitivity

The function respects letter casing by default. This means “HOME” is considered different from “home”. Let’s see what happens when we check for “HOME” instead:

Text.EndsWith( "Biking to home", "HOME" ) // Returns false

This is identical to:

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

By default, the function uses Comparer.Ordinal for case-sensitive comparison. So, “HOME” isn’t the same as “home”.

Ignoring Case Sensitivity

If you want the function to ignore letter casing and treat “HOME” and “home” as the same, you can use Comparer.OrdinalIgnoreCase. Here’s how:

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

Culture-Dependent Comparisons

The Text.EndsWith function can also handle culture-dependent comparisons using Comparer.FromCulture. For example:

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

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

In these examples, the characters “æ” and “ae” are treated as equivalent in English, but distinct in Danish.

Case Sensitivity with Cultural Comparisons

Even with cultural comparisons, case sensitivity still matters. You can change this behavior 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

Understanding the Text.EndsWith function and its options can help you handle text data more effectively in Power Query M. It allows for both case-sensitive and case-insensitive comparisons, as well as culture-dependent text handling.

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

Other functions related to Text.EndsWith are:

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