PowerQueryで正規表現を使う

JavaScriptの正規表現を呼び出す

残念ながら現時点では、PowerQueryのM言語では正規表現はサポートされていません。しかし、M言語でJavaScriptを呼び出すテクニックがあるそうで、これを使う事で正規表現が利用可能になるそうです。

replace(置換)とmatch(抽出)は先達の方々が日本語で解説記事を書いていたのでそのまま使えそうでしたが、test（True/False）は残念ながら見つけられませんでした。

仕事の関係でtestメソッドを使いたかったのでjavascriptのprogateの無料分だったり初心者向けサイトを見ながら書いてみました。下記を詳細エディタに貼り付ければ、そのままカスタム関数として使用可能です。

(string as text, reg_expression as text) as logical =>
let
  expressionText = Text.Replace(reg_expression, "\", "\\"),
  test =  Web.Page(
                "<script>
                        var originalText="&"'"&string&"'"&";
                        var re = new RegExp('"& expressionText & "');
                        var result=re.test(originalText);
                        document.write(result);
                </script>"){0}[Data]{0}[Children]{1}[Children][Text]{0},
  result = Logical.FromText(test)
in 
  result

利用例は下記のとおりです。

stringに判定したい文字列のカラムを指定、reg_expressionに正規表現を入れればTrue/Falseで結果を返してくれます。

上記の例においては、^\d{3}-\d{4}$ で、「三桁の数字で始まり、ハイフンのあとで四桁の数字で終わる」というパターンに合致するものがTrueとなっています。

セキュリティ対策の結果としてVBAに対する締め付けが日増しに強まる中、このテクニックを使うことでVBA無しのExcelで正規表現を利用することができます。私自身、正規表現を勉強し始めたばかりで自由に使いこなすには程遠いレベルですが、ちょっとした文字列の操作・処理を行う際に正規表現を用いることで面倒なIFやSWITCH、FINDなどの関数をネストする必要がなく簡潔に処理を行うことができ、その便利さに感動しております。~~試しておりませんが、PowerBIでも使えるはずです。~~

パフォーマンスについて（2022/9/7 一部追記）

こちらの方法は大変便利なのですが、欠点（？）として処理が重いようです。10万件程度のデータで試した際にレスポンスがよくない感じがしました。都度JavaScriptを呼び出しているからでしょうか？方法自体邪道ですので、大規模なデータを扱う際には元から用意されているＭ言語の関数を組み合わせた方が効率が良いはずです。少なくとも上記で例示している郵便番号が正しく入力されているか程度のものならば、正規表現を用いずに既存のM言語の関数を組み合わせて正しく書いた方がパフォーマンスは高いはずです。

本手法はExcelで使い捨ての小規模なデータ処理などをする際に、簡便的にメタ文字で雑にデータを捌く際に利用する位が適切でしょうか。用法容量を守って限定的に使う事をお勧めします。

Web.Page関数の利用について（2022/9/7追記）

TwitterにてTakeshi KagataさんからWeb.Page関数についてのコメントをもらいました。こちらの手法はJavaScriptを呼び出すにあたってM言語上でWeb.page関数を利用していますが、この関数はIE10が利用可能であることが前提条件になっています。そのためPower BI Service などのようなWEBサービス上では動作しないのではないかとのことでした。これをクリアするためには、ウェブブラウザを備えたシステム(オンプレミスデータゲートウェイ)を別途用意する必要があるそうです。

実際にPower BIをしっかりと運用したことがなかったので大変に勉強になりました。

また、Web.Page関数はIE10を利用しているため、当然ながら利用できる正規表現もIE10で利用可能なJavaScript由来に限定されます。従いまして、?<=肯定の後読み、?<!否定の後読みは残念ながら本手法では利用できません。残念、無念。

そもそも論として

スプレッドシートには正規表現の関数があるじゃないですか。Excelのワークシート関数、M言語、DAXのそれぞれで正規表現を実装してくれたらこんな面倒なことしなくても済むんですよね…。