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 scans a text string to determine if it contains a specified substring. An interesting feature of this function is its sensitivity to the case of the text, which can be controlled as needed.

Simple Case-Sensitive Example

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”. Since “Red” is indeed a part of “Red roses”, the function returns true.

As the example shows, the Text.Contains function is naturally case-sensitive. Observe another example:

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

This is identical to:

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

In both 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 the comparison is case-sensitive.

Making the Function Case-Insensitive

Need to make the function case-insensitive? You can achieve this with an optional third parameter, comparer, which controls the case sensitivity of the comparison:

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

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

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.

Culture-Dependent Comparisons

The Comparer argument isn’t limited to case sensitivity alone; it can also perform culture-dependent comparisons. For example, the English string “æ” is considered 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

Case Sensitivity in Culture-Dependent Comparisons

If you need to ignore case while performing a culture-dependent comparison, you can use 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

To conclude, the Text.Contains function can handle text searching tasks in case-sensitive, case-insensitive, and culture-dependent manners.

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy