Power Queryで正規表現を使用する方法

ブログ情報の移行

====

今回はPower Queryで正規表現を使う方法を紹介します。
Power Queryで正規表現を使う方法に関する記事を探していたら、こちらの記事がヒットしました。

このページに書かれている下記のコードを少し変えて解説していきたいと思います。

let
    fx=(x,y)=>Web.Page(
    "<script>
    var x = "&x&";
    var y = new RegExp('"&y&"','g');
    var b = x.match(y);
    document.write(b);
    </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in
    fx("""hello012中国1235""","\\d+")

コードの解説

Web.Page

Web.PageはHTMLを読み取ってアウトプットしてくれる機能をもっています。
https://learn.microsoft.com/en-us/powerquery-m/web-page

なのでその機能を使ってJavaScriptで正規表現の処理を行って返すための記載をそれ以降のコードで記載をしています。

“&&”の意味

テキストの中に変数を入れてつなげるために一度ダブルクォーテーション(“)でテキストを閉じてアンド(&)で繋げています。

JavaScriptだとプラス(+)でテキストをつなげますが、Power Queryの場合はアンド(&)で繋げることが可能です。

下記のようにテキストをつなげているために行っているということです。

let
    text = "World"
    output = "Hello " & text & "!"
in
    output //Hello World!

JavaScriptのコード

match関数を使って処理を行っているのは下記の部分になります。

 var x = "&x&";
 var y = new RegExp('"&y&"','g');
 var b = x.match(y);

正規表現で抽出したい変数bを作成したのちに document.write() で返すための処理を行っています。

[Data]{0}[Children]{0}[Children]{1}[Text]{0}

document.write()を使用するとテーブルやリストが作成されます。

Children{1}部分で評価したHTMLのheadではなくてbody部分を取ったりという指定でテキストを取得するために使用しています。

よりよい記載方法

このコード自体いけていないと思ったので、自分で書き直したコードがこちらになります。

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

in
    replacer("""hello012中国1235""","\d+")

ちなみに変数を指定する際はconst,letだと動かないのでvarで指定する必要があります。

ということでPower Queryで正規表現を使うためにはWeb.Pageを使ってJavaScriptで処理をする必要があるというお話でした。

次回は列の変更方法から実際に関数を列全体に実行する方法を紹介していきます。

====

今回は実際に正規表現でデータを整理していきたいと思います。

やることは下記の表のように日付型、テキスト、空白が混ざっている状態から何年かを数値データとして取りたいと思います。

設立年	抽出したいデータ
12/19	2020
2020年	2020
テキスト
2020/12 テキスト	2020

Power Queryにデータを取り込んで、設立年を項目名にした状態から追加の処理をしていきたいと思います。

正規表現で変換する関数の追加

詳細エディターを開くと下記の状態になっています。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}})
in
    変更された型

Excel.workbookのパスには選択したファイルの場所が入っています。

前回の記事で作成した正規表現で欲しいテキストを取得できる変数を追加します。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in
    replacer

前の記事と違っていて、数字全てを取得したくはないのでmatch部分は変更しています。

◆変更前

'"&char&"'.match(/"&reg&"/g);

◆変更後

'"&char&"'.match(/"&reg&"/)[0];

ちなみに関数を作成するとエディター画面で関数をテスト実行することが可能です。

仮引数分の枠が表示されるので、入力すると

無事「2020」と返って来ているのがわかります。

指定した列すべてに関数を実行する

指定列の値に関数を実行させる方法です。

Table.TransformColumnTypesでテキストへ変換

Table.TransformColumnTypesは下記のように引数を2つ指定します。

Table.TransformColumnTypes(テーブル名, {{項目名1, データ型1}, {項目名2, データ型2} ...})

データ型の種類は下記サイトに載っています。

https://learn.microsoft.com/ja-jp/powerquery-m/m-spec-types
ただ実際はここで調べるよりかは一度エディターでタイプ変換をして出てくる関数を見た方が早いと思っています。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (cha, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = Table.TransformColumnTypes(変更された型1, {{"設立年", type text}})

in
    result

次の2つの理由からまずはデータを全てテキスト型に変更します。

次に使う、Table.ToListで日付(数値)が混ざっているとエラーになる

1. 正規表現が使えるのはテキストデータのみ
2. 型変換と同時に列指定も可能なので移行の処理が行いやすくなります。

もし事前に全てテキスト型だときまっていたら、Table.SelectColumnsで列指定しても大丈夫です。

Table.TransformRowsでレコードを加工

Table.TransformRowsは引数を2つ取り、

Table.TransformRows(テーブル, レコード処理の関数)

と書きます。

レコードは each _ で全てのレコードを指定可能で一つのカラムを選択したい場合は[カラム名]で指定することが可能です。

今回は「設立年」を指定して各列の内容をreplacerで変換していきたいと思います。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = 
        Table.TransformRows(
            Table.TransformColumnTypes(変更された型1, {{"設立年", type text}})
            , each replacer([設立年], "\d+")
        )
in
    result

エディターで確認すると、数値が入っているものは2020が出力できていますが、テキストだけの場合やnullの場合はerrorが返ってきています。

エラー処理を行う

try otherwiseでエラー処理が可能です。

汎用的に書くと下記のようになります。

try 処理 otherwise エラー時の処理

具体的にクエリで試す時は下記を入れてみてください。

let
    func = (x) => List.Combine(x),
    output = try func("text") otherwise "error"
in
    output //error

func関数はエラーになるのでotherwiseの後の「error」が返ってきます。

これを使って先ほどの正規表現で数値を出力していた箇所にtry式を入れます。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = 
        Table.TransformRows(
            Table.TransformColumnTypes(変更された型1, {{"設立年", type text}})
            , each try replacer([設立年], "\d+") otherwise null
        )
in
    result

すると”テキスト”とnullだった値はnullが返ってくるようになります。

ここまで1列の変換する方法を見てきました。

ただこのままでは複数列があるテーブルでそのままでは使えないので、複数列で1列のみに関数を適用する方法を次で紹介していきたと思います。

===

今回は複数列がある場合に1列だけ指定する方法を見てきます。
下記のようにデータを増やして前のコードを実行してみます。

企業名	住所	設立年
AAA	東京都AAA	2020/12/19
BBB	東京都BBB	2020年
CCC	東京都CCC	テキスト
DDD	東京都DDD
EEE	東京都EEE	2020/12 テキスト

このデータに前回の記事で紹介したコードを出力は同じ状態となります。

コードはこちら。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = 
        Table.TransformRows(
            Table.TransformColumnTypes(変更された型1, {{"設立年", type text}})
            , each try replacer([設立年], "\d+") otherwise null
        )
in
    result

List.TransformMany(list as list, collectionTransform as function, resultTransform as function) as list

この状態に一緒に取得している「企業名」、「住所」も残す方法をこの記事で紹介していきます。

Table.FromRows()

Table.FromRows()は1つ目の引数に2次元のリスト,2つ目のリストで項目名を指定します。

Table.FromRows(二次元リスト, 項目名 as list)

項目名を空白にした場合はColumn1,Column2と連番で項目名が付けられます。
例としてはこちらを入れて動きを確認してみてください。

Table.FromRows({{1, "Bob", "123-4567"}, {2, "Jim", "987-6543"}}, {"CustomerID", "Name", "Phone"})

レコードの指定方法

レコードは下記のように表現されます。

recode = [recode1 = 1, recode2 = "A", recode3 = "one"]
recode[recode1] //1

JSONでいうkeyとvalueをセットで持っていて、object[key]で指定した内容を取得できるように、
レコード名[項目名]で指定列の内容を取得することができます。

Record.ToList()

Record.ToList()は引数にレコードを指定することによって、値のみを取得することが可能です。

Record.ToList(recode)
なので下記のようにレコードからリストを作成が可能です。

let
    get_recode = (row as record) => Record.ToList(row),
    recode = [recode1 = 1, recode2 = "A", recode3 = "one"]
in
    get_recode(recode) //{1, "A", "one"}

これを使ってレコードを渡したときにリストを返す関数を作成します。

return_list = (row as record) => Record.ToList(Record.RemoveFields(row, "設立年"))

今回はレコードを渡したときに設立年以外のデータを返したいので、Record.RemoveFieldsで「設立年」のみ削除しています。

設立年以外の項目を追加

前回までで書いた下記のコードに追加していきたいと思います。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    result = 
        Table.TransformRows(
            Table.TransformColumnTypes(変更された型1, {{"設立年", type text}})
            , each try replacer([設立年], "\d+") otherwise null
        )
in
    result

先ほど作成したreturn_list関数に加えて、Table.FromRowsメソッドで必要なリストでテーブルを作成できるように変更しています。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    return_list = (row as record) => Record.ToList(Record.RemoveFields(row, "設立年")),
    result = 
        Table.FromRows(
            Table.TransformRows(
                Table.TransformColumnTypes(変更された型1, {{"設立年", type text}}), (row) =>
                    List.Combine({{try replacer(row[設立年], "\d+") otherwise null}, return_list(row)})
            )
        )
in
    result

ただしこのままでは項目名が変わってしますので、それを汎用的に追加してきます。
汎用的にするためには下記のコードが良いかなと思っています。

let
    ソース = Excel.Workbook(File.Contents("パス.xlsx"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    変更された型 = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    変更された型1 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"設立年", type any}}),
    replacer = (char, reg) => Web.Page(
        "<script>
            var match = '"&char&"'.match(/"&reg&"/)[0];
            document.write(match);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    return_list = (row as record) => Record.ToList(Record.RemoveFields(row, "設立年")),
    result = 
        Table.FromRows(
            Table.TransformRows(
                Table.TransformColumnTypes(変更された型1, {{"設立年", type text}}), (row) =>
                    List.Combine({{try replacer(row[設立年], "\d+") otherwise null}, return_list(row)})
            ), List.Combine({{"設立年"}, List.RemoveItems(Table.ColumnNames(変更された型1), {"設立年"})})
        )
in
    result

これで下記のように無事に設立年以外のデータも取得できるようになりました。
以上でこのシリーズを終了します。