Power Queryで正規表現を使ってデータを整える

こんにちは！ウメハラ（plumfield56）です。

前回はPower Queryで正規表現を使う方法を紹介させて頂きました。

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

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

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

2020/12 テキスト	2020

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

正規表現で変換する関数の追加
指定した列すべてに関数を実行する
1. Table.TransformColumnTypesでテキストへ変換
2. Table.TransformRowsでレコードを加工
エラー処理を行う
このシリーズの記事

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

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

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} ...})

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

M 言語の型 - PowerQuery M

Power Query M 数式言語での型の使用について説明します

ただ実際はここで調べるよりかは一度エディターでタイプ変換をして出てくる関数を見た方が早いと思っています。

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で日付(数値)が混ざっているとエラーになる
正規表現が使えるのはテキストデータのみ

型変換と同時に列指定も可能なので移行の処理が行いやすくなります。

もし事前に全てテキスト型だときまっていたら、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列のみに関数を適用する方法を次で紹介していきたと思います。