Power Queryで複数のExcelブックをまとめる方法｜Excel.Workbookでシート・テーブルを指定して結合する

支店別や月別に分かれた同じ形式のExcelブックを1つの表にまとめたいときは、ブックを1つのフォルダに集め、Folder.Files でファイルの一覧を取り、Excel.Workbook で各ブックを開いて、結合するシートやテーブルを名前で指定します。

「データの取得」→「ファイルから」→「フォルダーから」で結合する方法もあります。ただしこの方法では、サンプルとして選んだファイルのシート名などを前提にした処理が自動で作られます。そのため、ファイルによってシート名が違う、余分なシートがある、列名が少し違う、といったときに原因を探しにくくなります。この記事では、各ステップで何をしているかが分かる形でクエリを組み立てます。

Excel.Workbookが返す表

Excel.Workbook(ブックのバイナリ, 見出しを使うか) は、ブックの中身を一覧にした表を返します。

シートとテーブルに同じ名前が付いていることもあるので、絞り込むときはNameとKindの両方を条件にします。

ブックの形式を自分たちで決められるなら、データ部分をテーブルにして名前をそろえておくほうが、結合は安定します。

手順1：フォルダのファイルをxlsxだけに絞る

「データ」→「データの取得」→「その他のデータ ソースから」→「空のクエリ」を選び、詳細エディターで次のように書き始めます。パスは自分のフォルダに置き換えてください。

let
    ソース = Folder.Files("C:\Data\支店別売上"),
    xlsxのみ = Table.SelectRows(ソース, each Text.Lower([Extension]) = ".xlsx" and not Text.StartsWith([Name], "~$")),
    列を絞る = Table.SelectColumns(xlsxのみ, {"Name", "Content"}),
    名前変更 = Table.RenameColumns(列を絞る, {{"Name", "ファイル名"}})

• 拡張子は大文字の場合もあるので、Text.Lower で小文字にしてから比べます
• 「~$」で始まるファイルは、ブックを開いている間に作られる一時ファイルなので除きます
• Folder.Files はサブフォルダ内のファイルも拾います。対象外のサブフォルダがある場合は、Folder Path列でも絞り込みます
• ファイルのName列は、後でブック内のName列と名前がぶつかるので、「ファイル名」に変えておきます

手順2：各ブックを開き、結合する対象を指定する

続けて、各ブックを開いて中身の一覧を展開し、対象を絞り込みます。

    ブック = Table.AddColumn(名前変更, "Book", each Excel.Workbook([Content], true)),
    展開 = Table.ExpandTableColumn(ブック, "Book", {"Name", "Kind", "Hidden", "Data"}, {"対象名", "種類", "非表示", "Data"}),
    対象 = Table.SelectRows(展開, each [種類] = "Table" and [対象名] = "T_売上")

シートを対象にする場合は、条件を次のように変えます。

    対象 = Table.SelectRows(展開, each [種類] = "Sheet" and [対象名] = "売上")

シート名がファイルごとに違うときは、次のように決まりを作って絞ります。

• 名前の付け方に決まりがある：Text.StartsWith([対象名], "売上")
• どのブックにもシートが1枚しかない：[種類] = "Sheet" だけで絞る
• 非表示のシートを除きたい：[非表示] = false を条件に加える

絞り込んだら、プレビューのファイル名列に、どのファイルもちょうど1回ずつ出ているか確認します。出てこないファイルは対象の名前が違っていて、2回以上出るファイルは条件に合う対象が複数あります。

シートの1行目が見出しではない場合

Excel.Workbook の第2引数 true は、シートの1行目を見出しとして使う指定です。1行目にタイトルなどがある場合は第2引数を書かずにブックを開きます。そのうえで、各シートの不要な行を飛ばしてから見出しに上げます。次の例は先頭の2行を飛ばしています。

    ブック = Table.AddColumn(名前変更, "Book", each Excel.Workbook([Content])),
    展開 = Table.ExpandTableColumn(ブック, "Book", {"Name", "Kind", "Hidden", "Data"}, {"対象名", "種類", "非表示", "Data"}),
    対象 = Table.SelectRows(展開, each [種類] = "Sheet" and [対象名] = "売上"),
    見出し調整 = Table.TransformColumns(対象, {"Data", each Table.PromoteHeaders(Table.Skip(_, 2), [PromoteAllScalars=true])})

飛ばす行数がファイルによって違う場合は、テンプレートをそろえるか、テーブルにしてもらうほうが確実です。

手順3：ファイル名を付けて結合する

どのファイルから来た行かが分かるように、各表にファイル名の列を付けてから Table.Combine で縦につなげます。完成したクエリは次のとおりです。

let
    ソース = Folder.Files("C:\Data\支店別売上"),
    xlsxのみ = Table.SelectRows(ソース, each Text.Lower([Extension]) = ".xlsx" and not Text.StartsWith([Name], "~$")),
    列を絞る = Table.SelectColumns(xlsxのみ, {"Name", "Content"}),
    名前変更 = Table.RenameColumns(列を絞る, {{"Name", "ファイル名"}}),
    ブック = Table.AddColumn(名前変更, "Book", each Excel.Workbook([Content], true)),
    展開 = Table.ExpandTableColumn(ブック, "Book", {"Name", "Kind", "Hidden", "Data"}, {"対象名", "種類", "非表示", "Data"}),
    対象 = Table.SelectRows(展開, each [種類] = "Table" and [対象名] = "T_売上"),
    ファイル名付き = Table.AddColumn(対象, "明細", each let f = [ファイル名] in Table.AddColumn([Data], "ファイル名", each f)),
    結合 = Table.Combine(ファイル名付き[明細]),
    型変更 = Table.TransformColumnTypes(結合, {{"日付", type date}, {"金額", type number}})
in
    型変更

Table.Combine は列名を手がかりに表を縦に積みます。一部のファイルにしかない列も結果に残り、その列がないファイルの行ではnullになります。列名の違いが結果に表れるので、ずれに気付きやすいのが利点です。

一方、Data列を「展開」ボタン（Table.ExpandTableColumn）で展開すると、展開する列名の一覧は、展開したときにプレビューで読み込まれていたデータから作られ、コードに書き込まれます。あとで別の列名を持つファイルが加わっても、その列は結果に出てきません。複数のブックを結合するときは Table.Combine を使うことをおすすめします。

データ型の設定は結合したあとに1回だけ行います。列名は自分のデータに合わせて置き換えてください。

列名がずれたときの確認

結合したあとに次のような症状が出たら、ファイルによって列名が違っています。

• 同じ意味の列が2列に分かれている（「金額」と「金額 」など）
• 一部のファイルの行だけ、ある列がnullになっている
• Column1、Column2のような列ができている

どのファイルの列名が違うかを見つけるために、確認用のステップを一時的に追加します。

    列名確認 = Table.AddColumn(対象, "列名", each Text.Combine(Table.ColumnNames([Data]), " | "))

このステップを選ぶと、ファイルごとの列名が1行に並ぶので、ほかと違うファイルを見つけられます。

これらの処理は、結合する前に各表へ適用します。たとえば、空白を除く処理と名前をそろえる処理を 対象 と ファイル名付き の間に入れる場合は、次のようにします。

    列名をそろえる = Table.TransformColumns(対象, {"Data", each Table.RenameColumns(Table.TransformColumnNames(_, Text.Trim), {{"売上額", "金額"}}, MissingField.Ignore)}),
    ファイル名付き = Table.AddColumn(列名をそろえる, "明細", each let f = [ファイル名] in Table.AddColumn([Data], "ファイル名", each f)),

見出しを使って結合しているなら、列の並び順が違っても Table.Combine は列名で合わせてくれます。見出しを使わず、Column1のような列名のまま結合している場合は、並び順の違いがそのまま列のずれになるので注意してください。

更新する前のチェック

• 対象のフォルダに、関係のないxlsx（このクエリの集計結果を保存したブックなど）を置いていないか
• パスワードが設定されたブックが混ざっていないか（開けないためエラーになります）
• 対象を絞り込んだ後の件数が、ファイルの数と同じになっているか
• 結合した後の行数と金額の合計が、元のブックの合計と合っているか

最後の2つは、ファイルを追加したり差し替えたりするたびに見ておくと、読み込まれていないファイルや二重に読み込まれたファイルに早く気付けます。