Press "Enter" to skip to content

Pattern-Matching and Text Extraction in Power Query

Imke Feldmann shows how we can match specific patterns in Power Query, which lacks regular expresssions:

I plan to approach this by

1. stepping through the string and check each character if it is valid.
2. If so, store it
3. and if not, forget it. And in that case, also forget the stored values so far so the collection starts from scratch.
4. Then if a series of matches builds up, I will have to check the count of the stored values to not exceed the length of my target pattern.
5. Once the length is reached, no further checks shall be performed and the found values be returned.

My aim is to find a generic way so that this solution can be adapted to many other similar use cases as well. Therefore the pattern must be described in a scalable way and the identification of the pattern elements should be easy to compute.

It’s good to be able to adapt, improvise, and overcome, though this is exactly what regular expressions are intended to do. It’s odd that there appears to be no built-in capability, where instead you’d have to do something like bring in external languages like JavaScript.