custom comparer function

[Anonymous]

In Power Query I like to write a custom comparer function, for instance:

Text.Contains("abc","abc", (s,t)=>Comparer.Ordinal(s,t)).

This results in an error message:

Expression.Error: The specified comparer does not support culture sensitive comparisons.

I cannot find any documentation for this error or how to resolve it.
Is it even possible to create a custom comparer for a function like Text.Contains or does it only support built-in comparers?

[artemus]

Yes, only built in comparitors are supported. You can use Text.ToList to get all the characters in the list

[Anonymous]

Thanks for your reply, artemus.

To elaborate a bit, I need a text comparison function that supports wildcards (both * and ?). Unfortunately this is not supported in Power Query, so I wrote my own function using Text.ToList and List.Generate. In fact I wrote a M language version of wild_match_iter on Wildcard Matching Methods (dogankurt.com)

It works just fine, but the solution with Lists is a complete performance killer.

I also made an iterative (non-recursive) version of a solution proposed by Chris Webb, see Chris Webb's BI Blog: Implementing A Basic LIKE/Wildcard Search Function In Power Query Chris Webb's... . Its fast but only supports * (or the % version in SQL Like terms), not ?.

So I guess my follow up questions are:

1. Is a fast and complete, custom made implementation of matching with wildcards possible in Power Query?
2. Will wildcard matching be supported by Power Query itself anytime near in the future?

[Rickmaurinus]

I doubt they will be supported anytime soon. Was hoping for REGEX before. But since there are so many different implementations of the topic, the PQ team has not decided to pick one. Nor that they will. 

Here's some more food for thought code mimicking wildcards in Power Query: https://gorilla.bi/power-query/wildcards/

At least it's something to start with.

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.