タイトル通りですが、パワークエリを使って、フォルダ指定で複数のブックを結合して読み込みたい時の話です。おそらく大半の人は「ファイルから」の「フォルダーから」を使って、「データの結合と変換」を選択しているのではないでしょうか。
それで全く問題ありませんし、慣れているならそっちのほうが使いやすいはずです。ただ作成されるヘルパークエリが、一見すると分かりにくいので苦手意識を持っている方も多いのではないかと思います。
これが今回の完成目標です。「フォルダから」を使って、複数のファイルを縦に結合して読み込む（「データの結合と変換」）と同じ動作を、別の方法でやってみます。
「参照ファイル」フォルダの中はこんな感じになっています。
任意のフォルダの中に xlsxファイルがいくつか入っている状態。
クエリを普通に「フォルダから」で作成すると、画像のような「ヘルパークエリ」が追加されるはずです。
慣れれば便利なんですが、ここでちんぷんかんになる（修正が発生した時に、どこをいじっていいか分からない）人も少なくないと思います。逆をいえば、ヘルパークエリを見てすんなり仕組みが分かる人なら、別のアプローチをしてもいいように思います。
ひとまずフォルダパスを指定し、フォルダコネクタを使ってファイル情報テーブルが表示されたクエリを作成します。
ソース = Folder.Files("C:\xxx\xxxx\sumple"),
ここから「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」の中に書けばいいです。最後の型の変更は、実際のファイルの見出しに合わせてやってください。
上記の流れは、いちいち全部 M言語で記述しなくても大半はマウス操作でできます。
マウス操作中心で編集する場合の手順
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 ソース = Folder.Files("C:\xxx\xxxx\sumple\"), 行の抽出 = 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 型の変更
