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

Power Query

こんにちは!ウメハラ(plumfield56)です。

Power Queryで正規表現を使用してデータと整えたいと思っています。
前回までで正規表現を列に適用する方法を紹介しました。

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

企業名住所設立年
AAA東京都AAA2020/12/19
BBB東京都BBB2020年
CCC東京都CCCテキスト
DDD東京都DDD 
EEE東京都EEE2020/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

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

このシリーズの記事

  1. Power Queryで正規表現を使用する方法
  2. Power Queryで正規表現を使ってデータを整える
  3. Power Queryで正規表現を使ってデータを整える②

コメント

タイトルとURLをコピーしました