本来、同形式の表はシート単位で分けてはいけないんだけども、印刷物感覚で表を作っている人は必ずといっていいほど、これをやってしまう。ひどい場合、何十枚どころか１００枚を超えてシートを増やしてしまう人もいる。こうなってしまったら手動でまとめるのは困難なので、マクロかクエリを使わざるを得なくなる。
この手の記事は結構あるので、細かい説明は端折るとして、一つのブックに複数のシートがあるものとする。
この内、対象外のシート（画像の「対象外」シート）を除外して
こんな感じに（シート名を入れて）まとめたい場合。
let
参照元 = "C:\……\サンプル.xlsx",
ソース = Excel.Workbook(File.Contents(参照元), null, true),
行の抽出 = Table.SelectRows(
ソース, each [Name]<>"対象外" and [Kind]="Sheet"
),
Data2列の追加 = Table.AddColumn(行の抽出, "Data2", each
let
Name=[Name],
ヘッダーに昇格 = Table.PromoteHeaders([Data]),
列の追加 = Table.AddColumn(
ヘッダーに昇格, "Name", each Name, type text
),
見出し = {"Name"}&Table.ColumnNames(ヘッダーに昇格)
in
Table.SelectColumns(列の追加, 見出し)
),
テーブルの結合 = Table.Combine(Data2列の追加[Data2])
in
テーブルの結合単純にはこんな形でまとめられる。今回はそれぞれのシートの表にテーブル書式が設定されていない前提にしているので、ヘッダーへの昇格を加えている。テーブルがあるならそっちを読み込んだほうが早い。
除外条件の「[Name]<>"対象外"」のところは、適宜変更する必要あり。
取り除きたいシートが複数ある場合は
not List.Contains({"対象外1", "対象外2",……}, [Name])
のようにして除外してもいい。List.MatchesAllを使う手もあるし、やり方は色々かと思う。
シートごとの見出しが一律でない（ただし位置は固定の）場合はこんな感じ。
ソース = Excel.Workbook(File.Contents("C:\……\xxx.xlsx"), null, true),
Table列の追加 = Table.AddColumn(ソース, "Table", each
let
Name = [Name],
リストに変換 = Table.ToRows([Data]),
上位行の削除 = List.Skip(リストに変換),
リストの編集 = List.Transform(上位行の削除, each {Name} & _),
テーブルに変換 = Table.FromRows(リストの編集)
in
テーブルに変換
),
Table列の結合 = Table.Combine(Table列の追加[Table])列名とかデータ型の指定とかは全部後回し。
