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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

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.