Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Wildcard Matching 2024

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 a...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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
// 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.

 

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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:

      EDIT: if fx="null" then false else true

       

      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=70ge7534tuugp17ottiw2wwya&st=s41o4w17&dl=1 

       

      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.

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        2 years ago

        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.

         

        Some of your function errors occur when the data type in text column is a number. These can be handled most simply by making that column text type.

         

        Others I need to look at more closely.

         

        So far as which to use, that's up to you. Obviously, if your actual use case involves data such as you present in your example table, a corrected routine of yours would be inarguably better. Under different use cases,  the time difference might be irrelevant.