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.