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.StartsWithtests the beginning of a value, such as a SKU prefix.Text.EndsWithtests the end of a value, such as a file extension.Text.Containstests 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.Containsis 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
- Vehicle Inventory Tracking Spreadsheet: Setup and Tips
- Upload a Budget Spreadsheet: Safe Steps and Options
- Vacation Budget Spreadsheet: Plan Costs and Track Spending
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns Shortcut: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then A, then E to open Text to Columns;…
- Power Query Excel: Split a Column into PartsIn Excel for Microsoft 365 on Windows, split a column in Power Query by opening the query, selecting…
- Excel Table Creation: How to Set Up and Use TablesTo create an Excel table in Microsoft 365 for Windows, select a cell in a contiguous data range,…
- Unmerge Excel Cells Without Losing DataTo unmerge cells in Excel for Microsoft 365 on Windows, select the merged cell or range, then choose…
- How to Paste in Excel While Skipping RowsTo place a list into Excel with blank rows between entries, put a formula in the first destination…
- Google Sheets Last Updated Timestamp: How to Find ItTo find when a Google Sheets file was last updated, open it and choose File > Version history…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.