Power Query Find Text in a String: M Functions and Examples

To find whether a Power Query text value contains a word or phrase, use Text.Contains. In a Custom Column, for example, enter Text.Contains([Description], "urgent", Comparer.OrdinalIgnoreCase) to return true when Description contains “urgent,” regardless of letter case, and false when it does not.

What Text.Contains does

Text.Contains is a Power Query M function that checks for a literal substring inside a text value. Its basic form is Text.Contains(text, substring); an optional comparer controls how the text is compared. The result is a logical value, meaning true or false, which makes the function useful for adding a flag, selecting rows, or building conditional logic.

The search is for a sequence of characters, not a whole word. If a cell contains “urgent,” then searching for “gent” also returns true. By default, matching is case-sensitive: “Urgent” and “urgent” are different. The function does not interpret wildcard characters or regular expressions. A search for "*urgent*" looks for those asterisks as literal characters; use just "urgent" to find the word within a longer value.

A useful distinction: Text.Contains answers whether a match exists, not where it occurs. If you need the position of a match, use Text.PositionOf instead.

Find a phrase in Power Query Editor

In Excel for Microsoft 365 on Windows, open the query in Power Query Editor and add a logical result column:

Select Add Column > Custom Column.
Give the new column a name, such as HasUrgent.
Enter Text.Contains([Description], "urgent", Comparer.OrdinalIgnoreCase) in the formula box, replacing Description with the name of your text column.
Select OK. The new column displays true or false for each row. Set or confirm its data type as True/False if needed.

The custom-column box expects an M expression, so do not put an Excel-style equals sign before it. Power Query supplies the row context for the expression; you refer to the current row’s field with square brackets, as in [Description]. Column names containing spaces or punctuation can also be referenced in brackets, for example [Customer Notes].

If you only want to keep rows that contain a phrase, use the filter on the text column instead: open its filter menu, choose Text Filters > Contains, and enter the search text. Filtering removes nonmatching rows from the query result; adding a custom column preserves the rows and labels which ones match. Choose based on whether you need to discard rows or keep a reusable match indicator.

Choose case-sensitive or case-insensitive matching

Without a comparer, Text.Contains uses a case-sensitive ordinal comparison. For many business searches, case-insensitive matching is more practical. Pass Comparer.OrdinalIgnoreCase as the third argument, as in the opening example. This treats “URGENT,” “Urgent,” and “urgent” as equivalent for the search.

For a deliberately case-sensitive check, make that choice clear by using Comparer.Ordinal as the comparer. Ordinal comparisons compare character values rather than applying language-specific text rules. If a source system follows particular linguistic or regional comparison rules, Power Query also provides culture-based comparers, but use one only when those rules matter to the data. Different comparison rules can affect results for accented letters and other characters, so test representative values before applying them across a dataset.

Adding a comparer is not the same as changing the text to uppercase or lowercase first. A comparer specifies how the comparison is performed without needing a separate transformation column. That can make the query easier to read and avoids relying on a particular capitalization format in the source.

Handle nulls, errors, and non-text values

Source columns are not always populated consistently. A blank value represented as null is different from an empty text value, and a null passed into Text.Contains can produce a null result rather than true or false. If you need every row to have a definite logical answer, handle null explicitly:

With the text field in a column named Notes, use this Custom Column expression to return false for null and otherwise check for “refund”: if [Notes] = null then false else Text.Contains([Notes], "refund", Comparer.OrdinalIgnoreCase). The expression returns false for a missing note and true for a note such as “Refund requested.”

A blank string, written as "", is still text. Since every text value contains the empty string, searching for an empty string is usually not a meaningful way to test whether a cell is blank. Test for null or an empty string separately if the requirement is to identify missing content.

Also check the column’s data type. If a field contains numbers, dates, or mixed values, Text.Contains may report a type error because it expects text. Convert values deliberately when that makes sense; for example, a numeric product code can be turned into text before searching. Do not automatically convert every field: date formatting and numeric formatting can affect the resulting text, and a conversion may hide inconsistent source data. If errors appear, inspect the error details and decide whether to correct, replace, or exclude the problematic values.

Search for several terms

For a short list of alternatives, test each term and combine the results with List.AnyTrue. With Notes as the text column, this expression returns true if the row contains either “urgent” or “overdue,” without regard to case:

if [Notes] = null then false else List.AnyTrue(List.Transform({"urgent", "overdue"}, (term) => Text.Contains([Notes], term, Comparer.OrdinalIgnoreCase)))

The null check prevents a missing Notes value from being treated as a match. List.Transform applies the search to each item in the list, and List.AnyTrue reports whether at least one search returns true. For a requirement that every listed term must be present, use List.AllTrue in place of List.AnyTrue. This is useful for a fixed, small group of terms; if the terms come from a separate table or users need to maintain them, a data-driven approach is easier to update than editing a long expression.

Text.Contains is not a pattern-matching or fuzzy-search function. It will not automatically match misspellings, word variants, or synonyms. For those needs, consider normalizing the data, using a suitable merge or fuzzy matching option where available, or defining explicit alternative terms.

Use positions, prefixes, or filters when appropriate

Use the function that answers the actual question rather than treating every text task as a contains search:

Text.Contains returns whether a substring occurs anywhere in the value.
Text.PositionOf returns the position of a substring, with positions counted from zero; it can return -1 when no match is found. For a case-insensitive position search in Description, use Text.PositionOf([Description], "urgent", Occurrence.First, Comparer.OrdinalIgnoreCase).
Text.StartsWith checks only the beginning of a value, while Text.EndsWith checks only the end. These functions also accept an optional comparer.
A text filter is convenient when the goal is simply to keep or exclude matching rows, rather than add a result column.

For example, a product code that must begin with “AB” is better checked with Text.StartsWith([ProductCode], "AB", Comparer.OrdinalIgnoreCase) than with Text.Contains, which would also match “XAB-12.” Picking the narrower function helps communicate the rule and prevents accidental matches.

Return a label instead of true or false

A match result can be used inside an if expression to assign a useful category. With Notes in the current row, enter if [Notes] = null then "No note" else if Text.Contains([Notes], "refund", Comparer.OrdinalIgnoreCase) then "Refund-related" else "Other". This returns “No note” for null, “Refund-related” when the text includes refund in any capitalization, and “Other” for the remaining text values.

Put the most specific conditions first when rules overlap. If a row can contain both “refund” and “replacement,” the first matching branch determines the label. Add another else if for each additional category, and consider a separate mapping table when the list of terms or labels becomes long. A mapping table is easier for colleagues to maintain than deeply nested conditions.

Refresh and troubleshooting considerations

Power Query applies the expression as part of the query and evaluates it again when the query refreshes. The result is not a live Excel worksheet formula that recalculates immediately as a cell is edited; it reflects the source data available at refresh time. After changing a source value, refresh the query to update the match column.

If a query is slow, the cause may be the source, the amount of data, earlier steps, or whether the connector can pass operations back to the source. Query folding varies by connector and operation, so do not assume that every text search is pushed to a database. In Power Query Editor, the View Native Query option may be available on a step for some sources; its availability and the generated query depend on the connector. If performance matters, test refresh time with realistic data and avoid adding unnecessary repeated transformations.

Common causes of unexpected results include using the wrong column name, searching with the wrong capitalization, checking a null value, or expecting wildcard behavior. Preview several known matching and nonmatching rows, including mixed-case and blank examples. If a filter seems to remove records unexpectedly, verify whether the text filter is applied at the step you intended and inspect the Applied Steps list.

Frequently Asked Questions

Is Power Query Text.Contains case-sensitive?
Yes, by default. Use Comparer.OrdinalIgnoreCase as the optional comparer to ignore case, or Comparer.Ordinal when you want to state explicitly that matching is case-sensitive.

How do I find a word in any part of a cell?
Add a Custom Column and use Text.Contains([ColumnName], "word", Comparer.OrdinalIgnoreCase), replacing the field and search term. The result is true when the sequence appears anywhere in the text.

How do I filter rows that contain text?
Open the text column’s filter menu and choose Text Filters > Contains. This keeps matching rows in the query output; use a custom column instead if you need to retain all rows and mark matches.

Can Text.Contains search for multiple words?
Yes. Apply it to each term in a list with List.Transform, then use List.AnyTrue for any-term matching or List.AllTrue when every term must be present. Add a null check if the source field can be null.

Why does Text.Contains return an error?
Check that the value is text and that the column reference is correct. A number, date, or error value may not be suitable input as-is. Handle nulls separately and convert non-text values only when the conversion produces the search behavior you intend.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.