Text.Contains

Updated on

Text.Contains is a Power Query M function that detects if a text value contains a specified substring. The function returns true if the substring is found, with optional comparers to specify case-insensitive or culture-aware comparisons.

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

Syntax

Text.Contains(
   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

Detects whether text contains the value substring. Returns true if the value is found. This function doesn’t support wildcards or regular expressions.

The Text.Contains function returns a Boolean value (either true or false) indicating whether a given text string (‘text’) contains a specified value (‘substring’). The comparison can be modified using an optional ‘comparer’ parameter. This function doesn’t support wildcards or regular expressions.

Examples

The Text.Contains is a Power Query function that is often used to provide information. It scans a text string and identifies if it contains a specified substring. An interesting facet of this function is its sensitivity to the case of the text, which can be controlled as needed.

Let’s break it down with a simple example:

Text.Contains( "Red roses", "Red" ) // Returns true

Here, the function checks if the text string “Red roses” contains the substring “Red”. As “Red” is indeed a part of “Red roses”, the function returns true.

However, the Text.Contains function is naturally case sensitive. Observe the following example:

Text.Contains( "Red roses", "red" ) // Returns false

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

Text.Contains( "Red roses", "red", Comparer.Ordinal ) 

In both these cases, the function checks if the lowercase “red” is present within “Red roses”. Since the text cases don’t align, the function returns false. The Comparer.Ordinal makes it explicit that we’re testing in a case-sensitive manner.

Need to make the function case-insensitive? That’s easy to accomplish with an optional third parameter, ‘comparer’, which lets us control the case sensitivity of the comparison:

Text.Contains( "Red roses", "red", Comparer.OrdinalIgnoreCase )
Text.Contains to test case insensitive in Power Query M

This time, even though “red” is in lowercase, the function returns true as Comparer.OrdinalIgnoreCase makes the function case-insensitive.

But the Comparer isn’t limited to case sensitivity alone; it can also perform culture-dependent comparisons. Take, for instance, the English string “æ”, which is deemed identical to “ae”. However, in Danish, these characters are distinct.

Text.Contains( "æ", "ae", Comparer.FromCulture( "en-US" ) ) // Returns true

Text.Contains( "æ", "ae", Comparer.FromCulture( "da-DK" ) ) // Returns false

Note that while Comparer.FromCulture considers culture-dependent equality, it still respects case sensitivity:

Text.Contains( "æ", "AE", Comparer.FromCulture( "en-US" ) ) // Returns false

However, if you need to ignore case while performing a culture-dependent comparison, you can leverage the optional argument of Comparer.FromCulture:

Text.Contains( "æ", "AE", Comparer.FromCulture( "en-US", true ) ) // Returns true

Text.Contains( "æ", "AE", Comparer.FromCulture( "en-US", false) ) // Returns false

In summary, the Text.Contains function can handle text searching tasks in case-sensitive, case-insensitive, and culture-dependent manners. It’s an effective way to analyze your text data in Power Query M.

Learn more about Text.Contains in the following articles:

Other functions related to Text.Contains are:

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