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

You can use Regular Expressions. This can be implemented in PQ for Power BI using Python or R, and in Power Query for Excel 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

 

EDIT: And I said I'm an Excel user, your workaround did not work in Excel.

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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. 

    To their own, but the JavaScript implementation certainly does work in Excel, I have used it many times.

     

     

     

     

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