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

 

EDIT: Code removed, follow up thread here:
https://community.fabric.microsoft.com/t5/Power-Query/Wildcard-Matching-2024-Attempt-2/m-p/4045339#M133230

 

Andreas.

 

 

 

 

 

 

 

 

 

 

 

 

 
 

 

 

 
 

 

 

 
 

 

 

Need Help

18 Replies

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