野口香社会保険労務士事務所

群馬県にある社会保険労務士事務所のページです。

  • 【Excel】PowerQueryで正規表現を使う方法【M式言語】

【Excel】PowerQueryで正規表現を使う方法【M式言語】

ExcelのPowerQueryで正規表現を使う方法をご紹介します。

目次

PowerQueryの起動

Excel上で、データ→データの取得→PowerQueryエディターの起動で起動できます。

PowerQueryの起動
PowerQueryの起動

テーブルを選択した状態で、データ→テーブルまたは範囲からでも起動できます。

テーブルまたは範囲から
テーブルまたは範囲から

PowerQuery詳細エディターの起動

PowerQueryエディターはこんな感じです。

PowerQueryエディター

使い方はあちこちにあるので、そちらを参照してください(他力本願)。

私はもっぱらGUIよりCUIの方が好きで、詳細エディターばかり使っています。

詳細エディターは、ホームタブのクエリグループの中にある詳細エディターを押すと起動します。

ホームタブ→クエリグループ→詳細エディター
詳細エディターを起動する
詳細エディター

M式言語

詳細エディターはM式言語というもので記述するらしいです。詳しくはMicrosoft社の説明を読んでください。

Power Query M 式言語

私のざっくりとした理解では、

  • letとinがある。
  • inにはoutputとなるものを書く(inという名前のくせに！！なお、深く考えず、単に変数名だけ書けばよいようだ)。
  • letには主に手続きを書く。
  • 手続きは上から順番に実行されている(・・・ように見せかけて、実は同時実行という噂)。
  • 変数の型宣言はかなり厳密らしい。でも型が分からなければ宣言を省略してOK。
  • たくさんの関数が用意されている。
  • この世界では大文字と小文字は区別される。
  • 文末はカンマにする。カンマが途切れたときがすべての手続きの終わりと認識される。

といったところでして、実はまだ全然分かっていません＞＜

次から次へと新しいものが出てきて、年々対応が難しくなっているのを感じる今日この頃です。

Excelのマクロ記録の機能と同様、詳細エディターでもデフォルトでテーブルを読み込んだときとかに、勝手に手続きが記述されています。

例えば、こんな感じのテーブルを読み込むと、

テーブルの例

こんな感じの記述をしてくれます。

自動で記述してくれた詳細エディターの例

このとき、「ソース」や「変更された型」というのは、自由に変更することができます。

2バイト文字は気持ち悪いので、私はさっさと変えてしまいます。

let
    t1 = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    q1 = Table.TransformColumnTypes(t1,{{"名前", type text}, {"種類", type text}})
in
    q1

あとはまあここに好きなようにコードを書き込んでいけばよいのです。

正規表現の使い方

PwerQueryでは正規表現があるのかないのか知らないのですが、今回参考にしたのがこちらです。

20221110追記。202210110現在リンク先が閲覧不能となっています。代わりにWayback machine(ネットアーカイブ)に残されていた記事のリンクを貼っておきます。

Power Queryで正規表現を使用する方法(元のURL ： “https://plumfield56.com/power-query/regex/”)

Power Queryで正規表現を使ってデータを整える(元のURL ： “https://plumfield56.com/power-query/regex2/”)

Power Queryで正規表現を使ってデータを整える②(元のURL ： “https://plumfield56.com/power-query/regex3/”)

HTMLでJavaScriptを使って正規表現を実装してしまおう！というスゴイ方法です。

ウメハラさんがネットで見つけた方法をカスタマイズして紹介していました。

ウメハラさんが参考にしたネットのやり方もリンクを貼っておきます(英語です)。

RegEx in Power BI and Power Query in Excel with Java Script

元ネタではreplaceでなくmatchを使っていました。

replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"®&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},

コードの意味はウメハラさんが詳しく説明しているので、ここでは省略します(他力本願)。

私の場合は検索でなく置換がしたかったので、replaceを使いました。

    replacer = (char, reg, after) => Web.Page(
        "<script>
            var newstr = '"&char&"'.replace(/"®&"/, '"&after&"');
            document.write(newstr);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},

ちょっと補足しておきます。

元ネタで[0]を使っているのですが、これは同一行で複数ヒットしたときに値を絞る意図だと思います。私もそうしたかったのですが、今回エラーが出るので外しました。テストに使ったデータが1行1ヒットしかしない前提で作ってあったためだと思うので、多分テストデータを変えればうまくいったのかもしれないのですけど、そこまでしている時間がありませんでした。

ちゃんとしたものを作るならgスイッチとかiスイッチとかもつけましょう！私はテスト時間がなくて今回だいぶはしょりました。

今回作ったモノ

今回知恵袋の質問が、

質問の内容

というものでしたので、本来であればCUIでなくGUIでの回答をご希望なのかな？と思いましたが、私の個人的な趣味で詳細エディターを使った手法を考えました。

二つのテーブルから、表記のゆれをなくす

Aというシートに名簿のようなテーブルがありまして、Bというシートには表記のゆれを修正する候補を列挙したテーブルがあります。

それぞれのテーブルを使って、右側のようなテーブルを作るというものでした。

let
    t1 = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    t2 = Excel.CurrentWorkbook(){[Name="テーブル2"]}[Content],
    L1 = t2[Keyword],
    regstr = Text.Combine(L1, "|"),
    replacer = (char, reg, after) => Web.Page(
        "<script>
            var newstr = '"&char&"'.replace(/"®&"/, '"&after&"');
            document.write(newstr);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = Table.AddColumn(
            t1,"変更後"
            , each try replacer([備考], ""®str&"", t2[After]{0}) otherwise null
        )
in
    result

ちょっと解説をしておくと、

L1 = t2[Keyword]で、テーブルt2のKeyword列をリスト化しています。

次の、regstr = Text.Combine(L1, “|”)というので、正規表現を作成しています。

これが実行されると、バレーボール|バリボー|バリボ|ﾊﾞﾘﾎﾞ|ﾊﾞﾚｰ|バレー観戦|女バレ|女ﾊﾞﾚという、|(パイプ)で連結した文字列が生成されますが、これが正規表現の選択肢となっているのです。

次のreplacer = …のところが自作関数です。自作関数が唐突に始まるあたり、可読性が低くなって読みづらいなあと思います。

result = …のところで自作関数を呼び出しています。t1というテーブルに列を追加する形でOutputすることにしました。

try…..otherwise…はエラーキャッチ文です。ウメハラさんが解説していますので、そちらを読んでください。

反省点

t2[After]{0}としているところが、挫折感ありますね。

テーブルt2のAfterという列の最初の行の値でもってすべて置換してしまっています。

この辺を何とかしたかったけど、正規表現で一気に置換している都合上、どうしようもなかったです。

それから、そもそも今回の問題は、別にPowerQuery使わなくても、Excelの機能だけでもなんとかなったんじゃね？？という気がしました。VBAなら余裕でできました。しかし、ご質問主様のご要望がPowerQueryだったので、まわりくどい気がしましたが、PowerQueryでやってみました。