Text.Contains
Text function · By Tahir Nas · Examples run in our simulator · Published · Updated
Tests whether text contains a substring.
When to use it
Use it to flag or filter rows by a keyword in free text, such as a description or a comment. Pass Comparer.OrdinalIgnoreCase unless case matters.
Syntax
Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logicalParameters
- comparer
- Comparer.OrdinalIgnoreCase for case-insensitive matching.
Returns
true or false.
Example
Text.Contains("Rebill request", "BILL", Comparer.OrdinalIgnoreCase)Result
true
Example: filter rows by keyword
Inside Table.SelectRows it keeps rows whose description mentions the word in any case.
Table.SelectRows(#table({"Desc"}, {{"Refund issued"}, {"New order"}, {"REFUND pending"}}), each Text.Contains([Desc], "refund", Comparer.OrdinalIgnoreCase))Result
| Desc |
|---|
| Refund issued |
| REFUND pending |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Case-sensitive unless you pass a comparer.
Common mistakes
- Forgetting the comparer and missing upper-case matches.
- Matching a short keyword that also appears inside other words.
Exercises that use Text.Contains
- intermediate~7 minPro
Flag tickets that mention billing, in any case (telecom)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
- advanced~12 minPro
List every validation issue per lead (events)
Check three rules on each sales lead and report the failed ones in one Issues column, keeping only rows with problems.
Learn more
Related functions
Official documentation: Text.Contains on Microsoft Learn.