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 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.
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.
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:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy