Power Query：縦に並んだ n行一組の項目別データを組み替える

他のアプリからデータを読み込んだら、こんな形だったってことが時々ある。

いわゆる n行一組のデータ（画像は6行）。これを項目別に組み換える場合、「数式かマクロか」になりがちだけど、クエリでももちろんできる。

よく見かける記事はインデックス列を追加→整数除算列を追加→剰余を求める→列のピボット……というパターン。セル範囲が「データ範囲」と名前定義されているものとしてクエリを作成する。

let
    項目名 = {"氏名","郵便番号","住所","電話番号","FAX番号","メールアドレス"},
    タイプ = {Text.Type,Text.Type,Text.Type,Text.Type,Text.Type,Text.Type},
    ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content],
    連番列の追加 = Table.AddIndexColumn(ソース, "連番", 0, 1, Int64.Type),
    整数除算列の追加 = Table.AddColumn(連番列の追加, "整数除算", each
        Number.IntegerDivide([連番], 6), Int64.Type
    ),
    剰余を計算 = Table.TransformColumns(整数除算列の追加,
        {"連番", each Text.From(Number.Mod(_, 6)), type text}
    ),
    列のピボット = Table.Pivot(
        剰余を計算, List.Distinct(剰余を計算[連番]), "連番", "Column1"
    ),
    列の削除 = Table.RemoveColumns(列のピボット,{"整数除算"}),
    列名の変更 = Table.RenameColumns(列の削除,
        List.Zip({{"0","1","2","3","4","5"},項目名})
    ),
    型の変更 = Table.TransformColumnTypes(
        列名の変更, List.Zip({項目名,タイプ})
    )
in
    型の変更

ひとまずはこれでできる。

ただどうも不細工な気がする。分割をもうちょっとスマートにできないものか。例えば Table.Splitを使ってやると下記のようにできる。

    テーブル分割 = Table.FromList(
        Table.Split(ソース,6), Splitter.SplitByNothing(), null
    ),
    行列入替 = Table.TransformColumns(
        テーブル分割, {"Column1", Table.Transpose}
    ),
    列の展開 = Table.ExpandTableColumn(行列入替, "Column1", 
        Record.FieldNames(行列入替{0}[Column1]{0}), 項目名
    ),
    型の変更 = Table.TransformColumnTypes(
        列の展開, List.Zip({項目名,タイプ})
    )

記述としてはこっちのほうが分かりやすいように思う。

Power Query：列名を統合してからクエリの追加（縦結合）を実行する

複数のテーブルをまとめる際、「クエリの追加（Table.Combine）」を使う前に必ずやっておかないといけないことがある。それは列名の統一。

例えば、下記のような列名が統一されていないテーブルが３つあるとする。

    テーブル1 = #table(
        type table [名前=text, 部署=text, 役職名=text],
        {
            {"鈴木", "総務部", "係長"},
            {"田中", "総務部", null}
        }
    ),
    テーブル2 = #table(
        type table [氏名=text, 部署名=text, 役職名=text],
        {
            {"山田", "人事部", "課長"},
            {"藤本", "人事部", "係長"}
        }
    ),
    テーブル3 = #table(
        type table [氏名=text, 部署名=text, 役職=text],
        {
            {"高橋", "営業部", "課長"},
            {"佐藤", "営業部", null}
        }
    )

やる前から想像がつくけど、この状態でクエリの追加（縦結合）してみる。

    そのまま結合 = Table.Combine({テーブル1, テーブル2, テーブル3})

すると、列名が異なるデータは別列にずれるので

こんな感じになってしまう。

こうならないようにするには、予め列名を統一しておく必要がある。色んなやり方があるけども、ひとまずの対処としては列名を統一するカスタム関数をクエリに組み込む方法が考えられる。

    変更用 = {"名前", "部署名", "役職名"},
    列名変換 = (テーブル) as table=>
        let
            変換リスト = List.Zip(
                {Record.FieldNames(テーブル{0}), 変更用}
            ),
            列名の変更 = Table.RenameColumns(
                テーブル, 変換リスト
            )
        in
            列名の変更

「変更前の列名リスト」は「Record.FieldNames」を使うことでリスト化できる。
ここまでくれば、後は変換して結合するだけ。いちいち Table.Combineを使わなくても「&」で繋いでしまえばよい。

        変換して結合 = 列名変換(テーブル1) & 列名変換(テーブル2) & 列名変換(テーブル3)

これで完成。もし「人事部のテーブルの列名が必ず正しい」という場合は、変更用リストをテーブル2から「Record.FieldNames」で読み込んでもいい。

Power Query：「年」を入力したら「1月～12月」の月初日が並ぶようにする

まずは A1に「年」を数値で入力したら、その年の「1月～12月」がテーブルで読み込まれるようにする場合。これは簡単。

ひとまず A1を「年」と名前定義しておいて、空のクエリの詳細エディタに下記を入力。

let
    年 = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
    月リスト作成 = Table.FromList(
        {1..12}, Splitter.SplitByNothing(), {"日付"}
    ),
    年月作成 = Table.TransformColumns(
        月リスト作成, 
        {"日付", each Date.From(Number.ToText(年 + _/100, "0.00"))}
    )
in
    年月作成

これで「1月～12月」が表示される。

「4月～3月」にしたい場合はリストを少し変える。

let
    年 = Excel.CurrentWorkbook(){[Name="年"]}[Content]{0}[Column1],
    月リスト作成 = Table.FromList(
        {4..12} & {101..103}, Splitter.SplitByNothing(), {"日付"}
    ),
    年月作成 = Table.TransformColumns(
        月リスト作成, 
        {"日付", each Date.From(Number.ToText(年 + _/100, "0.00"))}
    )
in
    年月作成

「1～3」ではなく「101～103」としているのがミソ。

こんな感じ。