Forum Discussion
Wildcard Matching 2024 Attempt 2
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:
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.
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=kw8ze79eytk7uzdpm4miugwdk&dl=1It 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=70ge7534tuugp17ottiw2wwya&dl=1If anyone finds an error, you can contact me directly:
andreas(dot)killer(at)gmx(dot)netAndreas.
3 Replies
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 RegExBest Regards,
Gao
Community Support TeamIf 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
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 => correctI 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.
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=kw8ze79eytk7uzdpm4miugwdk&dl=1It 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=70ge7534tuugp17ottiw2wwya&dl=1If anyone finds an error, you can contact me directly:
andreas(dot)killer(at)gmx(dot)netAndreas.