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| 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
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 trueHere, 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 )
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 falseNote that while Comparer.FromCulture considers culture-dependent equality, it still respects case sensitivity:
Text.Contains( "æ", "AE", Comparer.FromCulture( "en-US" ) ) // Returns falseHowever, 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 falseIn 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.
Related articles
Learn more about Text.Contains 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.Contains are:
