Wildcard Matching 2024 Attempt 2

[Anonymous]

I intentionally recreated this thread to make my point clear and concise.

I need a routine in Power Query that does a wildcard comparison. I need the Wildcards "*", "?" and "#".

I don't need any workarounds like RegEx or similar, please post suggestions in the previous post here:

https://community.fabric.microsoft.com/t5/Power-Query/Wildcard-Matching-2024/m-p/4042453#M132480 

I was able to write and optimize a function, since you apparently can't post code here that contains recursive calls due to the "at"-sign, here is the download link to the code:

https://www.dropbox.com/scl/fi/nw4nwwxa4g99dvbm0vl0s/fnCompareWildcards.pq?rlkey=kw8ze79eytk7uzdpm4m... 

It works well, except for one exception and I'm just trying to fix this error:

If pattern contains *# at the end or is followed by another wildcard, then in some cases the code returns TRUE, which is wrong. For example, compare "5a" with "*#".

I need help to fix this problem.

Andreas.

[Anonymous]

Hi @Anonymous ,

Please check the file:

The @ronrsnfld method also works, but some positions may require the addition of escape characters.

Here's a good website for testing Regex:
RegExr: Learn, Build, & Test RegEx

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Anonymous]

Hi Gao,

First of all, if you saw my previous post, I deleted it because my conclusions were wrong.

I tried your change (and others with List.Accumulate) and thanks to your idea I realized that this is the wrong part of the code.

The places where the wildcards start a new recursion are correct, the problem is the termination.
I have made some changes here and now it works almost perfectly.

Text: NLTGE2K0ID

Pattern: NLTGE2K*#? Result: true => wrong
Pattern: NLTGE2K*#?? Result: true => correct
Pattern: NLTGE2K*#??? Result: false => correct

I have a lot of randomly generated texts and search patterns, only search patterns that have these characteristics are missing, all others are correct.

If you download my code again and take a look, maybe you will have an idea. My feeling tells me that I have to somehow compare from the end to the front if the search pattern ends with a wildcard.

Andreas.

[Anonymous · Accepted solution]

After days of testing and testing in real life, it looks like my routine works perfectly.

Here is the link again:
https://www.dropbox.com/scl/fi/nw4nwwxa4g99dvbm0vl0s/fnCompareWildcards.pq?rlkey=kw8ze79eytk7uzdpm4m...

It is several thousand percent faster than RegEx, even with just a few comparisons the difference in runtime is clearly noticeable.

Here is the link to my test file:
https://www.dropbox.com/scl/fi/hnv86prqiewyajra5h0yr/CompareRegEx.xlsm?rlkey=70ge7534tuugp17ottiw2ww...

If anyone finds an error, you can contact me directly:
andreas(dot)killer(at)gmx(dot)net

Andreas.