Regex in PowerQuery

We desperately need the REGEX functions added to Power Query. Excel has already had these added in 2024 (REGEXTEST, REGEXEXTRACT, and REGEXREPLACE). See for reference Excel REGEX blog.

Yes, Python can be used for this, but this requires a Personal Gateway.

Yes, the javascript hack can be used for Power BI Desktop, but this does not refresh in the Power BI service.

This is a basic function that has also been applied in Power Apps Power Fx.

When dealing with messy data, it's often paramount to do some text transforms to clean it up a little. I end up writing a lot of regex and just having ChatGPT translate it into Power Query steps/functions because manually transforming text is such a pain in Power Query without regex. It's very quick for me to write regex due to how close the final syntax usually is to the text I'm matching.

For context, a simple regex-based prefix matcher like this:

^[A-Z]{3}-\d{4}\s*(?=\D)

Translates into a Power Query function along the lines of:

(text as nullable text) as nullable text =>
let
    t = text,

    Prefix = if t <> null and Text.Length(t) >= 8
        then Text.Start(t, 8)
        else "",

    ValidPrefix =
        Text.Length(Prefix) = 8 and
        Text.Middle(Prefix, 3, 1) = "-" and

        // First 3 uppercase letters
        List.AllTrue(
            List.Transform(
                Text.ToList(Text.Start(Prefix, 3)),
                each Character.ToNumber(_) >= 65 and Character.ToNumber(_) <= 90
            )
        ) and

        // Last 4 digits
        List.AllTrue(
            List.Transform(
                Text.ToList(Text.End(Prefix, 4)),
                each Character.ToNumber(_) >= 48 and Character.ToNumber(_) <= 57
            )
        ),

    AfterPrefix =
        if ValidPrefix
        then Text.Range(t, 8)
        else t,

    TrimmedAfter =
        if ValidPrefix
        then Text.TrimStart(AfterPrefix)
        else t,

    // Lookahead equivalent: next char must be non-digit (or absent)
    NextCharValid =
        Text.Length(AfterPrefix) = 0 or
        not List.Contains({"0".."9"}, Text.Start(TrimmedAfter, 1)),

    Result =
        if ValidPrefix and NextCharValid
        then TrimmedAfter
        else t
in
    Result