Excel Power Query : フォルダ指定でブックとシートを取得しシートを整形結合
[2025/12/20] 新記事
詳細エディタを使用せず、ヘルパークエリを使用する方法を記事にしました。
Excel Power Query Table.Combine を用いた複数ブック、複数シートの縦結合
https://bigarrow.tea-nifty.com/blog/2025/12/post-60e0ad.html
[2024/08/18] 本記事（旧）：ヘルパークエリを使用しない方法
１つのデータフォルダに入っている複数のデータブックとその中の同じ形の複数のデータシートを集計ブックに取り込み、各データシートを整形後に結合して1つのデータにする方法のメモです。これはCopilotの回答です。
■ クエリの作成手順
1) 例として1つのブックの１つのシートを取り込んでデータベース形式に整形するクエリを作成します。このクエリは最後に消してください。
2) 空のクエリを作成し、詳細エディタで下記の整形関数クエリに書き換え、例として作成したクエリの整形部分のコードを転記します。
3) 空のクエリを作成し、詳細エディタで下記の結合クエリに書き換えます。
■ Copilotへの質問と回答
Q:次のPower Query M式を教えてください。
1つのフォルダに複数のブックがあり、各ブックには複数のシートがあります。Power Queryのフォルダ指定で各ブックと各ブックの中の各シートを取り込み、共通の整形関数で各シートを整形した後に、整形した各表を結合します。各シートの形は同じとします。各ブックの数と各シートの数が変化してもクエリの変更なしに集計可能とします。
Q:整形関数をクエリ関数の形式にしてください。
A:もちろんです！以下に整形関数をクエリ関数の形式で示します。この関数は、各シートのデータを整形するために使用されます。
■ 整形関数クエリ：名前は"TransformFunction"にします。最初の"="の後ろから、最後の"in"の前までを空クエリに転記します。
// 整形関数
let
TransformFunction = (inputTable as table) as table =>
let
// 必要な整形処理をここに追加
TransformedTable = Table.TransformColumnTypes(inputTable, {{"列名1", type text}, {"列名2", type number}, ...})
in
TransformedTable
in
TransformFunction
この関数を使用して、各シートのデータを整形するために、先ほどのクエリに組み込みます。
■ 結合クエリ:フォルダのパスは、処理対象のブックが入っているフォルダに書き換えてください。
let
// フォルダのパスを指定
Source = Folder.Files("フォルダのパス"),
// Excelファイルのみをフィルタリング
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),
// 各ファイルの内容を展開
AddCustom = Table.AddColumn(ExcelFiles, "Custom", each Excel.Workbook(File.Contents([Folder Path] & [Name]), null, true)),
ExpandCustom = Table.ExpandTableColumn(AddCustom, "Custom", {"Name", "Data"}, {"SheetName", "Data"}),
// 整形関数を適用
ApplyTransform = Table.TransformColumns(ExpandCustom, {"Data", each TransformFunction(_)}),
// 整形したデータを結合
CombineData = Table.Combine(ApplyTransform[Data])
in
CombineData
各ファイルの展開をTable.ExpandTableColumns で行い、関数クエリ TransformFunction を使用して各シートのデータを整形し、整形した各シートの結合を Table.Combine で行っています。
■ [データ]-[データの取得v]-[ファイルから>]-[フォルダーから]ではうまく行かない。
1つしかシートが無いブックと複数のシートがあるブックで、且つ各シートのクロス表の横軸の項目名(例：横軸が日付のクロス表など)が異なる条件で、クリック操作でフォルダ指定の読込をしたところ、ヘルパークエリでエラーが出てうまく行きませんでした。ヘルパークエリでは、複数のシートがあるブックを一括で変換するサンプルクエリが作られてしまい、シートごとに変換する関数クエリになりませんでした。
■ クロス表の整形方法の参考情報
ピボット解除の応用技 簡単操作でマトリクス形式の表をデータベース形式に変換【エクセル パワークエリ】【Excel PowerQuery】
https://www.youtube.com/watch?v=fWw9ueVkHto
#2 表の変換にマクロなんかいらない【エクセル パワークエリ PowerQuery】1レコード複数行・複数列の表をリスト形式に変換する方法
https://www.youtube.com/watch?v=VleTkjBmaCA
ピボット解除応用：多階層クロス集計表のデータ化
https://modernexcel7.hatenablog.com/entry/2019/10/05/232407
【エクセル パワークエリ】 列が多段のマトリクス形式の表をリスト形式に変換する方法【Excel PowerQuery】【ピボット解除】
https://note.com/excel_dokata/n/neb26fe4b8331
【Excel】マウス操作だけのパワークエリからレベルアップ！カスタム関数クエリでワンランク上のデータ整形
https://www.youtube.com/watch?v=jDIwpBCVibY
■ ファイル結合の参考情報
この中にヘルパークエリを邪魔者あつかいしている人がいたら手を上げてください【Excel パワークエリ PowerBI】 / ExcelドカタCH
https://www.youtube.com/watch?v=uLV85sauJRw&t=862s
【Excel パワークエリ】ヘルパークエリなしでファイルを結合する方法 / ExcelドカタCH
https://www.youtube.com/watch?v=qriBiijC9WQ
■ Copilotの場所
ブラウザからBingのCopilotで質問するとPower Queryエディタのマウス操作の回答だけでコードが出てきませんでした。
WindowsのタスクバーのCopilotアイコンから質問するとコードを回答してくれました。それが上記回答です(上記回答は若干編集しています)。
無料版のChatGPTはコードを回答してくれましたが、理解できませんでした。
■ 余談
セルアドレスの沼から抜け出すためにモダンExcelを調べる機会が増えたのですが、ワークシート関数やVBAに比べてモダンExcelの情報が少ないです。
Power BIのDAXの記事はそれなりにあるのですが、Excel Power PivotのDAXは少し情報が少ない気がします。
Power Query M言語は更に情報が少なく、日本語の書籍も少ないです。Microsoft LearnのPower Query Mは、初心者には分かりにくいです。
VBAの構造化参照やデータ操作関数が使いやすくなれば、素人にも扱いやすくなると思うのですが、MicrosoftはVBAの更新には力を入れていない気がします。稀にExcelのUpdateでVBAが異常終了することがあるので、VBAのデグレードだけはしないようにして欲しいですね。
VBAでADO(Active Data Object)/SQLを使う方が自由度が高そうな気もしますが、プログラマではない一般ユーザーである私が用するには敷居が高いです。
■ 参考記事
Power Queryで複数の表を結合する方法（まとめ）：2025/11/06:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/11/post-341d5f.html
Power Queryで表をリスト形式に整形する方法（まとめ）:2025/11/16:当ブログ
https://app.cocolog-nifty.com/cms/blogs/632678/entries/95708707
複数ブック、複数シートをPower Queryで1つの表にする方法
https://bigarrow.tea-nifty.com/blog/2025/10/post-4ad540.html
Power QueryでExcelのシートを縦結合する方法
https://bigarrow.tea-nifty.com/blog/2025/02/post-b425f3.html
最近のコメント