なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:列名を統一してからクエリを追加(縦結合)する

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

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

    テーブル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(
                {Table.ColumnNames(テーブル), 変更用}
            ),
            列名の変更 = Table.RenameColumns(
                テーブル, 変換リスト
            )
        in
            列名の変更

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

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

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

型も一緒に指定したいなら少しやり方を変える。

    変更用 = type [
        名前=text, 部署名=text, 役職名=text
    ],
    テーブル結合 = List.Accumulate(
        {テーブル1, テーブル2, テーブル3}, #table(0, {}),
        (x, y)=>x & Table.FromRows(Table.ToRows(y), type table 変更用)
    )

テーブルを一旦リストにしてから結合してテーブルに変換し直し。