Excel Power Query Pattern Matching: Methods and Examples

How-to basics · Updated November 21, 2026

Excel Power Query pattern matching means filtering or transforming values according to text rules such as “contains this phrase,” “starts with these characters,” or “contains only digits.” In Power Query, use M functions such as Text.Contains, Text.StartsWith, and Text.EndsWith; for more involved rules, combine them or build a custom test.

What pattern matching means in Power Query

Power Query is Excel’s data-import and transformation tool. Its rules are written in the M language, either through the Power Query Editor interface or by editing the generated M code. A pattern-matching rule decides whether a value meets a text condition; that decision can then keep or exclude a row, create a new column, or classify the value.

For example, a product description might need to contain “wireless,” an order code might need to start with “AB-,” or a field might need to contain digits only. These are different kinds of tests, and choosing the right function makes a query easier to understand and maintain.

In the examples below, Source represents an earlier step in the query, such as the step that loads a table. The snippets are M expressions, not Excel worksheet formulas. They can be adapted in the formula bar or Advanced Editor in Power Query Editor.

Use built-in text functions for common patterns

For a simple substring test, Text.Contains checks whether one text value occurs inside another. This example keeps rows whose Description contains “wireless,” without distinguishing uppercase from lowercase. It also safely excludes null values:

Table.SelectRows(Source, each
    let
        value = [Description]
    in
        if value = null then false
        else Text.Contains(value, "wireless", Comparer.OrdinalIgnoreCase)
)

Table.SelectRows returns only rows for which its condition is true. The each expression evaluates the condition for the current row, and [Description] refers to that row’s Description field. Change the field name and search text to fit the table.

For predictable prefixes and suffixes, use the functions designed for those tests:

  • Text.StartsWith tests the beginning of a value, such as a SKU prefix.
  • Text.EndsWith tests the end of a value, such as a file extension.
  • Text.Contains tests for a phrase anywhere within the value.

For example, a row condition can combine a prefix and suffix test with and or or. Check for null before calling a text function, and use parentheses when combining several conditions so the intended logic is clear. These functions perform literal text checks; they do not interpret regular-expression syntax.

Does Power Query support wildcards or regular expressions?

Power Query M does not provide a built-in regular-expression matching function, and Text.Contains does not treat an asterisk as “any characters” or a question mark as “any single character.” If you search for "A*B" with Text.Contains, the asterisk is part of the literal text being searched for. This is a common source of confusion for people used to Excel worksheet functions or file-search tools.

For a simple wildcard-like requirement, translate the intent into text tests. “Starts with AB and ends with 9” can be expressed with Text.StartsWith and Text.EndsWith. “Contains either red or blue” can use two Text.Contains tests joined by or. A rule such as “an arbitrary number of characters followed by a particular ending” may be handled by checking only the ending, if the preceding characters do not need further validation.

When the requirement truly needs regex features—such as optional groups, character ranges, or a complex repeated pattern—consider doing that step in a system or tool that supports regular expressions, or redesign the rule as smaller, explicit tests in M. Do not paste regex notation into a Power Query text function and assume it will be evaluated as a pattern.

Match one of several terms

When a description can contain any one of a list of search terms, use a list and test each term. List.AnyTrue returns true if at least one test succeeds. The null check avoids passing a null value to Text.Contains:

Table.SelectRows(Source, each
    let
        value = [Description]
    in
        if value = null then false
        else
            List.AnyTrue(
                List.Transform(
                    {"red", "blue"},
                    (term) => Text.Contains(value, term, Comparer.OrdinalIgnoreCase)
                )
            )
)

This keeps a row when Description contains “red” or “blue,” regardless of case. To require every term instead, use List.AllTrue in place of List.AnyTrue. That distinction matters: “contains one of these words” and “contains all of these words” are different filters.

For a short, fixed list, separate conditions joined with or may be easiest to read. For a list maintained by users or stored in another table, keeping the terms as data is often more convenient. You can then adjust the list without rewriting a long condition, though the query will need to retrieve and evaluate that list correctly.

Check formats and character content

Pattern matching can also mean checking which characters a value contains. M’s Text.Select returns only characters from a specified set. To keep values made entirely of digits, compare the selected characters with the original value and reject empty strings:

Table.SelectRows(Source, each
    let
        value = [Code],
        digits = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}
    in
        if value = null then false
        else value <> "" and Text.Select(value, digits) = value
)

This is a character-content test, not a complete numeric or business-validation rule. For instance, it rejects a decimal point, a minus sign, and spaces. If those are allowed, define the permitted characters or use a conversion test suited to the required format. Also decide how to treat leading zeros: a code such as 00125 is often an identifier, not a number to convert.

For a multi-part format, combine simpler checks. A code might need a particular prefix, a fixed length, and digits in its remaining positions. Keep each requirement explicit and test sample values at the boundaries, including nulls, empty strings, and values with unexpected spaces.

Build a pattern filter in the Power Query interface

In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range to open Power Query Editor. Select the text column, open its filter dropdown, and choose Text Filters. Options such as Contains, Begins With, and Ends With let you create common filters without writing M directly. The exact dialog wording can vary slightly with the selected operation.

Power Query records interface actions as query steps and generates M code. To inspect or edit the code, use View > Formula Bar for the selected step, or choose Home > Advanced Editor to view the whole query. The interface is a good starting point; examining the generated step is useful when you need to add a comparer, handle nulls, combine conditions, or reuse a list of terms.

On Mac, Excel’s Power Query availability and interface can differ by edition and update channel. Where Power Query Editor and the relevant import feature are available, the same M concepts apply, but do not assume every Windows ribbon command or dialog is in the same place. Excel for the web also has a different set of authoring capabilities; use the desktop editor when a step requires editing M code and that option is not available in the browser.

Case sensitivity, spaces, and null values

Text comparisons should state the intended case behavior. M provides comparers such as Comparer.Ordinal and Comparer.OrdinalIgnoreCase for text operations that accept a comparer. Ordinal comparison is case-sensitive; the ignore-case version treats letter case as equivalent. For example, a test for “usb” with the ignore-case comparer can also match “USB.” This does not automatically remove leading or trailing spaces.

If extra spaces are accidental, clean the value before matching, for example with Text.Trim. If repeated internal spaces or inconsistent punctuation matter, decide whether to normalize them too; trimming the ends alone will not make "ACME-CO" equal to "ACME CO". Avoid changing source values more broadly than the rule requires, especially when punctuation or spacing carries meaning.

Null is not the same as an empty string. A null means there is no value, while "" is a text value with zero characters. Decide whether each should match, be excluded, or be categorized separately. A reliable query handles nulls explicitly before applying text functions, rather than relying on every incoming row to have a clean text value.

Common errors and ways to troubleshoot them

  • Wrong data type: A column may contain numbers, dates, or mixed values rather than text. Set the column’s type appropriately or deliberately convert values before applying text operations.
  • Null-related errors: Add an explicit null branch, such as returning false for null values, before calling a text function.
  • Literal asterisks: Remember that an asterisk passed to Text.Contains is literal, not a wildcard.
  • Unexpected case results: Pass the intended comparer instead of assuming case-insensitive behavior.
  • False negatives: Check for leading spaces, nonbreaking spaces, punctuation, and alternate spellings in the source data.

When a filter removes more rows than expected, create a temporary custom column that returns the result of the test, then inspect both the source text and the true/false output. This separates a problem in the condition from a problem in later query steps. Remove the diagnostic column when the rule is confirmed.

Performance and query folding

For large data sources, a filter may be faster when the source system can perform it rather than having Power Query retrieve every row first. This is called query folding: Power Query translates supported steps into operations understood by the source. Whether a specific text function, comparer, or combination folds depends on the connector and source system, so there is no universal rule that a particular pattern will fold.

Where the editor offers a query-folding indicator or a native-query view for the connector, use it to check what is happening. A custom function, a conversion, or a more elaborate list-based condition can affect folding, but the outcome varies. For a large query, filter early when appropriate, avoid unnecessary transformations, and compare refresh behavior with representative data. Correct results come first; a simpler condition is not useful if it changes the intended matches.

When fuzzy matching is a better fit

Pattern matching applies a rule you specify; fuzzy matching looks for similar text values. In Excel’s Power Query merge workflow, fuzzy matching can help connect text entries with spelling differences, such as a misspelled organization name and its intended counterpart. In the Merge Queries dialog, the fuzzy-matching option provides settings for controlling the match behavior. The available settings and results should be reviewed for the Excel version and connector in use.

Fuzzy matching is not a replacement for a precise rule. It may find plausible similarities that are not actually the same entity, and it does not mean “match this regular expression.” Use it for approximate text joins when review is possible. Use explicit text conditions for deterministic rules such as a required prefix, a known phrase, or an allowed character set.

Frequently Asked Questions

Can Power Query use * and ? as wildcards?
No. Functions such as Text.Contains treat those characters as literal text. Recreate a simple wildcard rule with text functions, or use a tool that supports regular expressions for complex patterns.

How do I make Text.Contains ignore capitalization?
Pass Comparer.OrdinalIgnoreCase as the optional comparer, as in Text.Contains(value, "usb", Comparer.OrdinalIgnoreCase). Handle null values separately before calling the function.

Can I search for several words at once?
Yes. Combine individual Text.Contains tests with or, or apply List.Transform to a list of terms and check the results with List.AnyTrue. Use List.AllTrue when every term must occur.

How do I match text at the start or end of a value?
Use Text.StartsWith for a prefix and Text.EndsWith for a suffix. These functions can also take a comparer for case behavior.

Is Power Query pattern matching the same as Excel Find or worksheet wildcards?
No. Power Query uses M functions and their documented comparison behavior. Worksheet search, worksheet formulas, and Power Query filters are separate features, so wildcard rules from one should not be assumed to apply to another.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.