Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Wildcards are characters that help you easily filter specific texts. They have a wide range of use cases, especially when dealing with filtering records in SQL databases. But what happens when you’re in the Power Query M environment? Can you still use the flexibility of wildcards?

Unfortunately, Power Query M doesn’t natively support wildcards. But that doesn’t mean you’re out of luck. In this article, we’ll explore various workarounds to mimic SQL wildcards in Power Query M.

Table of contents

Overview of Wildcards in SQL

So what are wildcards, and how do they work in SQL? A wildcard is a special character that represents other characters in search strings. You can place these characters within the string that you are searching for, and this alters the filtering behaviour. For example, the % wildcard in SQL matches zero or more characters. SQL has several types of wildcards, including %, _, [ ], and ^.

Sample Dataset

For the examples in this article, we’re using a simple dataset with just five words. This will help us focus on the actual functions and queries.

Dataset for Filtering with Wildcards in Power Query M

Simulating SQL’s % Wildcard

In SQL, the percentage sign (%) serves as a wildcard character. It allows you to match zero or more characters in a string. For example, if you’re searching through a database for all words that begin with the text “Bl” you’d write the query as follows:

SELECT * FROM myTable WHERE Words LIKE 'Bl%';

This SQL query will fetch all rows where the name of the text that starts with “Bl,” such as “Blaze,” or “Blade.”

Starting Text Matches

Power Query might not offer wildcards like % out of the box, but that doesn’t mean you’re out of options. One highly effective workaround is to use the Text.StartsWith function. This function works by looking for values in a column that begin with a particular substring. The basic syntax of the function is quite simple:

Table.SelectRows( 
  Source, 
  each Text.StartsWith( [CustomerName], "Bl") 
)

Here, Source is the table you are querying, ColumnName is the specific column you’re interested in, and "Substring" is the text you are searching for.

Important

One subtle but crucial difference between SQL’s % wildcard and Power Query’s Text.StartsWith is the handling of case sensitivity. In SQL, the LIKE statement is generally case-insensitive. Power Query, however, is case-sensitive by default.

A more similar approach that ignores case, adjusts the above search to one that ignores the case by using Comparer.OrdinalIgnoreCase:

Table.SelectRows( 
  Source, 
  each Text.StartsWith( [Words], "Bl", Comparer.OrdinalIgnoreCase )
)
Using Text.StartsWith as wildcard in Power Query M

This modified function will match all entries that start with “Bl,” regardless of case, closely mimicking SQL’s % wildcard behaviour.

Ending Text Matches

In SQL, the ‘%’ wildcard isn’t just useful for matching text that begins with certain characters; it’s equally powerful for locating text that ends with specific characters. For example, if you’re interested in finding all records where the “Words” field ends with “ze,” you’d use the query ‘%ze’.

In Power Query, the native function to achieve this is Text.EndsWith. This function inspects each row’s specified column to see if it ends with a given substring. It then returns a filtered table containing only those rows that meet the condition.

Here’s how you can use Text.EndsWith to simulate SQL’s ‘%’ wildcard for ending texts:

Table.SelectRows( 
  Source, 
  each Text.EndsWith( [Words], "ze", Comparer.OrdinalIgnoreCase ) 
)
Using Text.EndsWith as wildcard in Power Query M

Simulating SQL’s _ Wildcard

The _ wildcard represents a single character. It can be any character or number, but each _ represents one, and only one, character. For example, a where statement that filters Words to be ‘_laze’.

Note

Some tools use the ‘?‘ wildcard instead of an underscore to represent this operation, for example, in a Microsoft Access Database. Power Query doesn’t have an out-of-the-box function for this, but luckily you can build the logic yourself.

Let’s dive into the “_laze” example to see how this can be done step-by-step.

Single Character _

The following statement takes your input as variable ‘a’, in this case “_laze”. It then finds the position of the underscore (b) and removes the character at that position (c) from the target column [Words]. It then removes the underscore from the value we’re searching for (d) and compares which rows are now equal to each other.

Table.SelectRows( Source, 
  each 
  [ a = "_laze", 
    b = Text.PositionOf( a, "_"  ),
    c = Text.RemoveRange( [Words], b, 1 ), 
    d = Text.Remove( a, "_" ),
    e = c = d ][e] 
)

The code uses quite a few steps, but once you set it up, you can easily copy-paste it. Or even better, turn it into a function!

Multiple Characters _

A downside of the above approach is that it only works with a single underscore. Let’s say we also want it to work for a value like “B_a_e”.

We would adjust our approach to first find all positions of the character ‘_’ (positions). We then turn the target value into a list of single-character text values (charList). We then create a list of offset positions of each value and zip the original list with this list (zippedlist).

Next, we use List.Transform to return null for each value that equals one of the positions of the ‘_’ (replaceWnull). We then remove the null values from the list (cleanedList) and merge the characters back together (finaltext). We remove all underscores from the searched value (cleanSearchValue) and compare it with the transformed value (result). You now end up with all the characters that match your search.

Table.SelectRows (
  Source, 
  each [
searchvalue  = "B_a_e",
charList     = Text.ToList ( [Words] ),
_positions   = Text.PositionOf ( searchvalue, "_", Occurrence.All ),
retrieveList = List.Difference( {0..Text.Length(searchvalue) }, _positions ),
extract      = List.Transform( retrieveList, each charList{_}? ),
result       = Text.Remove( searchvalue, {"_"}) = Text.Combine( extract )
][result]
)

Implementing Range Wildcards: [ ]

The [ ] wildcard in SQL allows you to provide a range of acceptable values in a string manually. For example, the string “Bla[dzf]e” would allow the values “Blade” and “Blaze”. By default, this search is performed in a case-insensitive way.

Here’s how you can do it:

Table.SelectRows (
  Source, 
  each [
    search         = "Bla[dzf]e", 
    searchposition = Text.PositionOf ( search, "[" ), 
    searchText     = Text.BetweenDelimiters ( search, "[", "]" ), 
    searchList     = Text.ToList ( searchText ), 
    cleanSearch    = 
      Text.RemoveRange ( search, searchposition, Text.Length ( searchText ) + 2 ), 
    cleanWords   = Text.RemoveRange ( [Words], searchposition, 1 ), 
    wildcardChar   = Text.Range ( [Words], searchposition, 1 ), 
    wildcardMatch  = 
      List.Contains ( searchList, wildcardChar, Comparer.OrdinalIgnoreCase ), 
    result         = cleanWords = cleanSearch and wildcardMatch
  ][result]
)

Excluding Characters with the ^ Wildcard

When used in square brackets, the ^ wildcard indicates that a position in your string cannot contain a range of values. It is similar to the previous wildcard but then performs the opposite filtering operation.
Here’s how you can do it. You can find the changes in lines 4, 6, 9 and 13.

Table.SelectRows (
  Source, 
  each [
    search         = "Bla[^zf]e", 
    searchposition = Text.PositionOf ( search, "[" ), 
    searchText     = Text.BetweenDelimiters ( search, "[^", "]" ), 
    searchList     = Text.ToList ( searchText ), 
    cleanSearch    = 
      Text.RemoveRange ( search, searchposition, Text.Length ( searchText ) + 3 ), 
    cleanWords   = Text.RemoveRange ( [Words], searchposition, 1 ), 
    wildcardChar   = Text.Range ( [Words], searchposition, 1 ), 
    wildcardMatch  = 
      not List.Contains ( searchList, wildcardChar, Comparer.OrdinalIgnoreCase ), 
    result         = cleanWords = cleanSearch and wildcardMatch
  ][result]
)

Conclusion

While Power Query M may not offer built-in wildcard capabilities like SQL, there’s a lot you can do to approximate the same functionality. Functions like Text.StartsWith and Text.EndsWith make it straightforward to replicate the % wildcard’s job of finding strings that begin or end with certain text.

However, when it comes to emulating more complex wildcards like _, [ ] or [^ }, things get a bit trickier. Building your own logic is then the most feasible option, requiring a more complex arrangement of M language functions.

Advanced Editor Code

In case you want to play around with the code, you can find it all together right here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspJTElVitUBs6ogLHdkVhGE5QZhxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Words = _t]),
    #"'Bl%'" = Table.SelectRows(
    Source, 
    each Text.StartsWith( [Words], "Bl", Comparer.OrdinalIgnoreCase) 
),
    #"'%ze'" = Table.SelectRows( 
  Source, 
  each Text.EndsWith( [Words], "ze", Comparer.OrdinalIgnoreCase ) 
),
    #"'_laze'" = Table.SelectRows( Source, 
  each 
  [ a = "_laze", 
    b = Text.PositionOf( a, "_"  ),
    c = Text.RemoveRange( [Words], b, 1 ), 
    d = Text.Remove( a, "_" ),
    e = c = d ][e] 
),
    #"'B_a_e'" = Table.SelectRows (
  Source, 
  each [
    searchvalue      = "B_a_e", 
    positions        = Text.PositionOf ( searchvalue, "_", Occurrence.All ), 
    charList         = Text.ToList ( [Words] ), 
    zippedlist       = List.Zip ( { charList, List.Positions ( charList ) } ), 
    replaceWnull      = 
      List.Transform (
        zippedlist, 
        each if List.Contains ( positions, _{1} ) then null else _{0}
      ), 
    cleanedList      = List.RemoveNulls ( replaceWnull ), 
    finalText        = Text.Combine ( cleanedList ), 
    cleanSearchValue = Text.Remove ( searchvalue, "_" ), 
    result           = finalText = cleanSearchValue
  ][result]
),
    #"[ xxx ]" = Table.SelectRows (
  Source, 
  each [
    search         = "Bla[dzf]e", 
    searchposition = Text.PositionOf ( search, "[" ), 
    searchText     = Text.BetweenDelimiters ( search, "[", "]" ), 
    searchList     = Text.ToList ( searchText ), 
    cleanSearch    = 
      Text.RemoveRange ( search, searchposition, Text.Length ( searchText ) + 2 ), 
    cleanWords   = Text.RemoveRange ( [Words], searchposition, 1 ), 
    wildcardChar   = Text.Range ( [Words], searchposition, 1 ), 
    wildcardMatch  = 
      List.Contains ( searchList, wildcardChar, Comparer.OrdinalIgnoreCase ), 
    result         = cleanWords = cleanSearch and wildcardMatch
  ][result]
),
    #"[ ^xxx ]" = Table.SelectRows (
  Source, 
  each [
    search         = "Bla[^zf]e", 
    searchposition = Text.PositionOf ( search, "[" ), 
    searchText     = Text.BetweenDelimiters ( search, "[^", "]" ), 
    searchList     = Text.ToList ( searchText ), 
    cleanSearch    = 
      Text.RemoveRange ( search, searchposition, Text.Length ( searchText ) + 3 ), 
    cleanWords   = Text.RemoveRange ( [Words], searchposition, 1 ), 
    wildcardChar   = Text.Range ( [Words], searchposition, 1 ), 
    wildcardMatch  = not List.Contains ( searchList, wildcardChar, Comparer.OrdinalIgnoreCase ), 
    result         = cleanWords = cleanSearch and wildcardMatch
  ][result]
)
in
    #"[ ^xxx ]"
Share on:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.