Excel Power Query Wildcards: What Works and What Doesn’t

How-to basics · Updated November 21, 2026

Excel Power Query does not interpret the asterisk (*) and question mark (?) as wildcards by default. Its built-in text filters search for literal text; to get wildcard-style results, use a suitable M text function for simple patterns or create a custom matching function for patterns that combine * and ?.

What wildcards mean in Power Query

In many Excel worksheet functions and dialog boxes, an asterisk represents any number of characters and a question mark represents one character. Power Query uses the M language instead, and its text functions do not automatically apply those wildcard rules. For example, a filter for text containing * looks for an actual asterisk character, not for every possible string.

This distinction matters when moving a task from a worksheet into a query. A pattern that works in a worksheet function such as COUNTIF may not work the same way in Power Query. M provides functions for specific tests—such as whether text contains a phrase or starts with a prefix—but does not provide a general wildcard operator or a built-in regular-expression match function.

Power Query is often the better place to do repeatable filtering of imported data: define the steps once, then refresh the query when the source changes. The important part is to translate the intended rule into M rather than assume that worksheet wildcard syntax carries over.

Filter a column in the Power Query interface

For a straightforward text filter, you can use the interface without writing M. In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, select the filter arrow on the relevant column, and choose Text Filters followed by an option such as Contains, Begins With, or Ends With. Enter the text to test, confirm, and then choose Home > Close & Load when you are ready to return the result to Excel.

These options express literal text conditions, not wildcard patterns. Choosing Contains and entering east finds values containing that text; entering *east* searches for the asterisks as part of the text. If the desired condition is “starts with AB,” use the Begins With filter and enter AB, rather than typing AB*.

The precise editor layout can vary slightly with the Excel build and the connector, but the filter arrow and text-filter choices are the key controls. Excel for Mac also includes Power Query features, though availability and interface details can differ by version. If a command is missing, check that Excel is up to date and that the query is open in Power Query Editor rather than the worksheet.

Translate simple wildcard patterns into M

For a pattern with one clear meaning, choose the M function that represents it. These functions test text values directly and can be used in a filter step. For example, the following expressions show the condition that belongs after each in a Table.SelectRows step:

  • Text.StartsWith([Code], "AB") keeps values beginning with AB, equivalent to the simple pattern AB*.
  • Text.EndsWith([Code], "2026") keeps values ending with 2026, equivalent to *2026.
  • Text.Contains([Region], "east") keeps values containing east, equivalent to *east*.

For instance, if a Code column contains AB17, ABX, and ZXAB17, a starts-with test for AB keeps AB17 and ABX, but not ZXAB17. A contains test for AB keeps all three. Picking the right test is more reliable than trying to imitate wildcard syntax where it is not supported.

You can put a condition into a row-filter step in the formula bar or Advanced Editor. A complete example is Table.SelectRows(Source, each Text.StartsWith([Code], "AB", Comparer.OrdinalIgnoreCase)). It keeps rows whose Code begins with AB, ignoring differences between uppercase and lowercase. Replace Source with the name of the preceding query step and Code with the actual column name in your data.

Use a custom function for * and ? patterns

If you need patterns such as A?7*, a custom M function can implement the familiar rules: * matches zero or more characters, and ? matches exactly one character. The function below compares characters exactly, so its matching is case-sensitive. It treats a null input or pattern as no match.

In Power Query Editor, create a blank query, open Home > Advanced Editor, replace the editor contents with the function, and name the query WildcardMatch. Then use the function in a filter or custom column. The function is intentionally self-contained; it does not interpret a backslash or another character as an escape for a literal * or ?.

(value as nullable text, pattern as nullable text) as logical =>
if value = null then false else
if pattern = null then false else
let
    Match = (vi as number, pi as number) as logical =>
        if pi = Text.Length(pattern) then vi = Text.Length(value)
        else if Text.Middle(pattern, pi, 1) = "*" then
            if pi = Text.Length(pattern) - 1 then true
            else List.AnyTrue(
                List.Transform(
                    {vi..Text.Length(value)},
                    (nextVi) => @Match(nextVi, pi + 1)
                )
            )
        else if vi = Text.Length(value) then false
        else if Text.Middle(pattern, pi, 1) = "?" or Text.Middle(pattern, pi, 1) = Text.Middle(value, vi, 1) then
            @Match(vi + 1, pi + 1)
        else false
in
    Match(0, 0)

After naming the query, add a custom column with WildcardMatch([Code], "A?7*"), or use that expression as a row condition: Table.SelectRows(Source, each WildcardMatch([Code], "A?7*")). The pattern matches A followed by any single character, then 7, then any remaining characters. It matches A17 and AX7more, but not A7 or AX77 if the required character positions do not fit. Check the results on a small sample before applying a custom matcher to a large table.

Case sensitivity, nulls, and column types

Case handling is worth deciding explicitly. M text comparisons are case-sensitive unless you supply a comparer where the function allows one. For example, Text.Contains([Region], "east", Comparer.OrdinalIgnoreCase) makes the contains test case-insensitive. The custom function above does not take a comparer, so East and east are different. If case should not matter, normalize both sides consistently or adapt the function to compare normalized text.

Text functions expect text. If the source column contains numbers, dates, or mixed values, first confirm the type shown by the column icon in Power Query Editor. Convert the column to text when a text match is genuinely intended; otherwise, use a comparison suited to the actual data type. A null is not the same as an empty string, and a custom function should define what to do with nulls rather than letting unexpected source values cause errors.

Be cautious when changing types: converting a date or number to text changes what its displayed representation looks like, and that representation can depend on formatting or locale. For example, filtering a date as text is usually less robust than filtering it as a date. Keep the column typed according to its meaning unless the matching rule specifically requires text.

Common problems and how to diagnose them

  • The filter returns no rows: Check whether the pattern was entered literally, whether the column is text, and whether case differs. Asterisks in a normal Text Filters search are characters, not operators.
  • Rows with similar values are missing: Decide whether the rule is starts with, ends with, or contains. Those tests are not interchangeable. Also inspect leading or trailing spaces in the source.
  • The step reports an error: Check the column name, its data type, and whether any rows contain nulls or non-text values. A custom function with nullable inputs can help make the null rule explicit.
  • Refresh becomes slow: A recursive matcher that tries many possible positions can do much more work than a simple prefix or contains test. Filter the source earlier, limit the rows being checked, or reduce a complex pattern to simpler conditions where possible.

When diagnosing a query, select the step immediately before the filter and inspect representative values. Power Query applies steps in order, so an earlier type conversion, trim, or filter may have changed the values before the wildcard-like condition runs. Use Applied Steps to move between stages and identify where the results first differ from what you expect.

Power Query wildcards versus worksheet wildcards

Worksheet functions and Power Query solve related but different problems. Excel worksheet functions such as COUNTIF support wildcard criteria in their own syntax; Power Query M text functions use explicit operations such as Contains, StartsWith, and EndsWith instead. A worksheet formula that accepts AB* as a criterion does not establish that a Power Query filter accepts the same pattern.

The tilde character is used in Excel wildcard criteria to escape wildcard characters in contexts that support that syntax. Do not assume that this worksheet convention applies to M. With Text.Contains([Code], "*"), the asterisk is simply a literal character in the search text. In the custom function shown above, by contrast, * and ? are deliberately treated as operators, and there is no escape syntax for searching for them literally.

Use the interface or simple M functions for routine prefix, suffix, and substring filtering. Reserve a custom matcher for cases where the pattern genuinely needs character-by-character rules. That choice makes the query easier to read, easier to maintain, and often faster to refresh.

Frequently Asked Questions

Does Power Query support wildcards in Text Filters?
No. The standard text-filter options search for literal text or apply a specific condition such as contains, begins with, or ends with. They do not interpret * and ? as wildcard operators.

How do I filter for values that start with certain letters?
Use the column filter arrow, then Text Filters > Begins With, and enter the prefix without an asterisk. In M, use Text.StartsWith([ColumnName], "prefix").

How do I find an actual asterisk in a value?
Use a literal search such as Text.Contains([ColumnName], "*"). In this function, the asterisk is an ordinary character, not a wildcard.

Can I use regular expressions in Power Query?
Power Query M does not include a general built-in regular-expression matching function. You can build a custom M function for a limited pattern language, use straightforward text functions, or process the data in another tool that supports the required pattern syntax.

Can a Power Query wildcard filter ignore case?
For simple tests, pass Comparer.OrdinalIgnoreCase to a text function that accepts a comparer, such as Text.Contains or Text.StartsWith. The custom function above is case-sensitive unless you modify it.

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.