Wildcard Matching 2024

[Syndicate_Admin]

Hi,

First of all, I'm not a BI user, I do support for Excel.

Wildcard comparisons in PQ are a real challenge and everything I've found so far are rudimentary solutions or workarounds to the actual problem. And everything was created several years ago.

Now I've tried it myself and found a solution that works (for my purposes), but it contains at least one error.

The code below supports the wildcards "*", "?" and "#". One problem is when the search pattern contains a "*" before a "#" and I just can't see a solution to the problem. For example, comparing "08erig1" and "*8*#*1" should give a FALSE as result...

If anyone has an idea how to fix the error or has a better solution, I'm grateful for any suggestions.
Or if anyone notices any other problems, I'd like to know about them too. 😀

Andreas.

let
  fnCompareWildcards = (expression as any, pattern as any, optional comparer as nullable function) as logical =>
    let
      pList = Text.ToList(if pattern=null then "" else pattern), 
      tList = Text.ToList(if expression=null then "" else expression), 
      pCount = List.Count(pList), 
      tCount = List.Count(tList), 
      Compare = if comparer = null then Comparer.OrdinalIgnoreCase else comparer, 
      Match = (pIndex as number, tIndex as number) as logical =>
        if pIndex >= pCount then
          tIndex >= tCount
        else if tIndex >= tCount then
          pList{pIndex} = "*"
        else if pList{pIndex} = "?" then
          @match(pIndex + 1, tIndex + 1)
        else if pList{pIndex} = "*" then
          if pIndex=pCount then
            true
          else
            List.AnyTrue(List.Transform({tIndex..tCount}, each @match(pIndex + 1, _)))
        else if pList{pIndex} = "#" then
          if pIndex=pCount then
            List.Contains({"0".."9"}, tList{tCount})
          else
            if List.Contains({"0".."9"}, tList{tIndex}) then
              @match(pIndex + 1, tIndex + 1)
            else
              false
        else
          not Logical.From(Compare(tList{tIndex}, pList{pIndex})) and @match(pIndex + 1, tIndex + 1), 
      result = Match(0, 0)
    in
      result,
documentation = [
  Documentation.Name =  "fnCompareWildcards",
  Documentation.Description = "Compares expression with pattern",
  Documentation.LongDescription = Documentation.Description,
  Documentation.Category = "String",
  Documentation.Source = "andreas.killer@gmx.net",
  Documentation.Version = "1.0",
  Documentation.Author = "Andreas Killer",
  Documentation.Examples = {[
    Description = "
fnCompareWildcards(""Andreas Killer 1964"", ""*as*er?19##"")
", 
    Code = "
expression : The text to compare
pattern    : The pattern containing wildcards as follows:

Char - Matches in expression
   ? - Any single character
   * - Zero or more characters
   # - Any single digit (0..9)

comparer : A Comparer used for controlling the comparison, if omitted Comparer.OrdinalIgnoreCase.
", 
    Result = "
true or false"]}]
in  
  Value.ReplaceType(fnCompareWildcards, Value.ReplaceMetadata(Value.Type(fnCompareWildcards), documentation))

[Syndicate_Admin]

You can use Regular Expressions. This can be implemented in PQ for Power BI using Python or R, and in Power Query for Excel or Power BI using a javascript construction as a custom function.

Here is an example of a javascript implementation in PQ for Excel to extract a substring. 

//see http://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/
// and https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf

let   fx=(text,regex)=>
    Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"&regex&"','g');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx

 There are other examples using Python or R in PQ for Power BI.

[Syndicate_Admin]

Please don't be mad, I'm not interested in more workarounds.

[Syndicate_Admin]

Not mad at all. But I think what you are really looking for is implementation of wild cards in M code. You're doing that with your custom function. The JavaScript implementation is also a custom function using only functions that are available in M

, unlike the python or r implementations which require something external. 

So I don't really see it as any more of a workaround than your custom function.

I agree the use of python or or might be considered a workaround since they require external libraries to be installed.

[Syndicate_Admin]

If you look back to my first post, I said my code did not work comparing "08erig1" and "*8*#*1" should give a FALSE as result.

You query did not work at all for that:

In real life I ​​need a wildcard routine that has a very simple syntax because the end user specifies the mask. If I come to my customer with RegEx, he'll kick me out. No normal end user uses that, it's far too complicated to get the correct pattern.

* ? and # is pretty simple to use for everyone and that's what I'm after. My code works to filter filenames that contains names and years, but I know it's not perfect, there is a bug that I can't find.

I can't use calls to external libraries, I don't know if every end user really has that or if the admin has disabled it for security reasons. I need a solution in MCode, no detours.

To my 2nd question: Any hint how to post an MCode with recursive calls here and prevent this forum editor to modify the code?

Andreas.

[Syndicate_Admin]

Now that I realize the limitations of the person entering the mask, your post makes more sense. If it were me, I would write a routine that translates the user input into a valid JavaScript regex and then feed that into the regex function I posted. 

So far as your second problem, I don't have a clue.I've yet to figure out what invalid HTML means in that context. Perhaps you could post a link to a text file that you've uploaded someplace. 

• Unfortunately, I can't work on a conversion routine right now as my computer is having fits leaving unable to run programs from my main drive. I somehow am in the position of having to rebuild a raid 1 array from scratch unless someone comes up with an idea that circumvents that necessity.

[Syndicate_Admin]

I've looked around a bit and a conversion should be possible with a few small replacements.

One difficulty is that the dot in RegEx itself is a wildcard, all the sources I've found say you should use \. instead of . and if I want to ignore the spelling, I should append /i.

Everything else later and thus a simple test:

// fnRegEx
let
  fx = (text, regex) =>
    Web.Page(
      "<script>
            var x='"
        & text
        & "';
            var y=new RegExp('"
        & regex
        & "','g');
            var b=x.match(y);
            document.write(b);
        </script>"
    )[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in
  fx

// Test_fnRegEx
let
    a = fnRegEx("qweXxls", "qwe.xls"),   // Result: qweXxls => wong
    b = fnRegEx("qweXxls", "qwe\.xls"),  // Result: qweXxls => wrong
    c = fnRegEx("Qwe.xls", "qwe\.xls/i") // Result: null => wrong
in
    c

Doesn't even come close to working. What now?
Does the RegEx that we call with the script use a different syntax??

Andreas.

[Syndicate_Admin]

Finally got my computer working.

For your three wild cards which I assume as similar meanings as the Like vba operator, something like:

(text,regex)=>

let 
    regList = Text.ToList(regex),
    translate = 
        List.ReplaceMatchingItems(regList, List.Zip({{"*","#","?","."}, {".*","[0-9]",".","\\."}})),
    reg = Text.Combine(translate,""),


    fx = Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"& reg &"','g');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
  fx

Results

fnRegexExtr("08erig1", "*8*#*1") => null
fnRegexExtr("qweXxls", "qwe.xls") => null

[Syndicate_Admin]

Hi Ron,

thanks for that function and that you take up this challange. If I assume correctly then you know me and you know what you are getting yourself into. 😁

You are correct VBA.Like is my proof function. And as I need a true/false result I modified the last line of your function:

if fx="null" then true else false

If this is wrong, or you have a better solution, tell me. I will make a completely fair comparison.

Here is my test file:
https://www.dropbox.com/scl/fi/hnv86prqiewyajra5h0yr/CompareRegEx.xlsm?rlkey=70ge7534tuugp17ottiw2ww... 

In C1 is the number of test patterns that are generated if you click the Generate button.

The query steps and used VBA codes are exactly the same for both queries to obtain a comparable result (as best we can do). If you have any concerns about how I did it, tell me. I only want to make a fair comparison of both methods, nothing else.

In column O we can see there are a lot of cases where your function fails, more then 50% is wrong.

Once you have resolved these problems, increase the number of test patterns to 10000, click the Generate button, a blink later they are generated. Click the refresh button above TestCompareWildcards, my function need around a half second.

Click the refresh button above TestRegEx... and take a coffee break.

To be fair, my function is also still not perfect. Therefore, the comparison is not yet really meaningful. But given these time differences, do you think RegEx is a really viable solution?

Andreas.

[Syndicate_Admin]

Yes, my regex function is much slower than yours.

1. You can shorten the last line of my function to "fx <> null" and it will return the logical
2. The errors with my function are from two issues, both of which can be fixed simply in the code:
   1. Case sensitivity, handled by an argument in the regex call:
      1. "var y=new RegExp('"& reg &"','i');"
   2. Your function apparently needs the entire word to match, and not just part of the word. So this needs the addition of the boundary tokens to the regex translation.

Having said that, it still takes a lot longer than yours.

Your function errors occur only when the data type in text column is a number. This can be handled most simply by making that column text type.

So just do that in your Data query:

#"Changed Type" = Table.TransformColumnTypes(Data_Table,{{"text", type text}, {"mask", type text}, {"fnLIKE", type logical}})