タイトル通り。フォルダ指定で複数のブックを結合して読み込みたい時。
これが今回の完成目標。「フォルダから」を使って複数のファイルを縦に結合して読み込むのと同じ動作を、別の方法でやってみる。
「参照ファイル」フォルダの中はこんな感じ。
任意のフォルダの中に xlsxファイルがいくつか入っている状態。
クエリを普通に「フォルダから」で作成すると画像のような「ヘルパクエリ」が追加されるはず。
中身はそれほど複雑ではないけど、若干見づらい。慣れれば便利ではあるけど、ちんぷんかんになる人も少なくないだろう（修正が発生した時に、どこをいじっていいか分からないとか）。逆をいえばヘルパクエリを見て仕組みが分かる人なら、別のアプローチをしてもいいように思う。
ひとまずフォルダパスを指定してフォルダコネクタを使ってファイル情報テーブルを読み込むクエリを作成。
フォルダパス = "C:\xxx\xxxx\sumple", ソース = Folder.Files(フォルダパス),
ここから「Name列からファイルリスト」「Content列からブック（シート）情報を読み込むカスタム関数」をそれぞれ作る。
方法１ファイルリスト = ソース[Name], ブック読み込み = (fn as text)=> let フォルダ = ソース{[Name=fn]}[Content], ブック = Excel.Workbook(フォルダ), シート = ブック{[Item="Sheet1",Kind="Sheet"]}[Data], ヘッダーへの昇格 = Table.PromoteHeaders( シート, [PromoteAllScalars=true] ), ブック名追加 = Table.AddColumn( ヘッダーへの昇格, "ブック名", each fn, type text ), 列の並べ替え = Table.ReorderColumns(ブック名追加, {"ブック名"} & Table.ColumnNames(ヘッダーへの昇格) ) in 列の並べ替え,
型の変更も入れておいたほうがいいけど、今回は列名の構造を任意と考えるので省略。
後は List.Generate関数を使って、それぞれのファイル情報を関数で読み込んで結合する。
リスト生成 = List.Generate(
()=>[i=0, tbl=#table(null, {})],
each [i]<=List.Count(ファイルリスト),
each [i=[i]+1, tbl=[tbl] & ブック読み込み(ファイルリスト{i-1})],
each [tbl]
),
テーブル結合 = List.Reverse(リスト生成){0}デフォルトの方法よりはすっきりしたものの、どうもあんまりうまくない。そもそも List.Generateは必要なのか？
Folder.Filesで作成されたテーブルにテーブル列を追加して、それを展開してやればいいんじゃないだろうか。とりあえず見た目はそっちのほうがすっきりする。
方法２行の抽出 = Table.SelectRows(ソース, each [Extension] = ".xlsx"), テーブル列の追加 = Table.AddColumn(行の抽出, "テーブル", each [ ブック指定 = Excel.Workbook([Content]), インポート = ブック指定{[Item="Sheet1",Kind="Sheet"]}[Data], ヘッダに昇格 = Table.PromoteHeaders(インポート) ][ヘッダに昇格]), 列の選択 = Table.SelectColumns(テーブル列の追加,{"Name", "テーブル"}), 列名の変更 = Table.RenameColumns(列の選択, {"Name", "ファイル名"}), テーブルの展開 = Table.ExpandTableColumn( 列名の変更, "テーブル", Table.ColumnNames(列名の変更{0}[テーブル]) ), タイプリスト = {{"日時", type date}, {"数値", type number}}, 型の変更 = Table.TransformColumnTypes(テーブルの展開, タイプリスト)
個別のブックでやる編集は「テーブル列の追加」の「let～in」の中に書けばいい。最後の型の変更は、実際のファイルの見出しに合わせてやってね。
もちろん上記の流れは、いちいち全部記述しなくても大半はマウス操作でできる。
マウス操作中心で編集する場合の手順
1. 「Extension」列のフィルタで「.xlsx」を抽出
2. カスタム列の追加（テーブル名：Table）
= Table.PromoteHeaders(
Excel.Workbook([Content]){[Name="★シート名"]}[Data],
PromoteAllScalars=true]
)
3. 「Name」「Table」列以外を削除
4. 「Table」列を展開
シート名が固定ではなく「先頭のシート」のように指定したい場合は「{[Item="Sheet1",Kind="Sheet"]}」を「{0}」とすればいい。シート名を追加するなら下記の形で。
方法２のおまけlet フォルダパス = "C:\xxx\xxxx\sumple.xlsx", ソース = Folder.Files(フォルダパス), 行の抽出 = Table.SelectRows(ソース, each [Extension] = ".xlsx"), ブック列の追加 = Table.AddColumn( 行の抽出, "ブック", each Excel.Workbook([Content]) ), シート名列の追加 = Table.AddColumn( ブック列の追加, "シート名", each [ブック]{0}[Name], type text ), テーブル列の追加 = Table.AddColumn(シート名列の追加, "テーブル", each [ インポート = [ブック]{[Name=[シート名]]}[Data], ヘッダに昇格 = Table.PromoteHeaders(インポート) ][ヘッダに昇格]), 列の選択 = Table.SelectColumns( テーブル列の追加,{"Name", "シート名", "テーブル"} ), 列名の変更 = Table.RenameColumns(列の選択, {"Name", "ファイル名"}), テーブルの展開 = Table.ExpandTableColumn( 列名の変更, "テーブル", Table.ColumnNames(列名の変更{0}[テーブル]) ), タイプリスト = {{"日時", type date}, {"数値", type number}}, 型の変更 = Table.TransformColumnTypes(テーブルの展開, タイプリスト) in 型の変更
