Poewr Query：フォルダを指定して複数のブックを結合して読み込む

これは今回の完成目標。

前回書いたけれども、セルに参照元となるパスを入力しておくと色々便利。

Power Query：セル値をパラメータにしてファイルを参照する

フォルダパス
=LET(_fn,CELL("filename",A1),LEFT(_fn,FIND("[",_fn)-1))

こんな形で同階層のフォルダパスを読み込めるので、そこから子フォルダを指定して外部ファイルからデータを読み込むことができる。同じく、「フォルダから」を使って複数のファイルを読み込む処理も、もっと簡単に行うこともできる。

「参照ファイル」フォルダの中はこんな感じ。

つまり同階層に作った子フォルダの中にファイルがいくつか入っている状態。

この場合、先程の「フォルダパス」の数式に「&"参照ファイル\"」を追加してやれば、親ファイルに連動する子フォルダのフォルダパスが完成する。ただし、OneDriveで同期しているファイルの場合、CELL関数で呼び出されるアドレスが微妙に変わってしまうので、別途頭の部分の置換が必要になる。要注意。

では肝心のブックの読み込み。クエリを普通に「フォルダから」で作成すると画像のような「ヘルパクエリ」が追加されるはず。

中身はそれほど複雑ではないけど、やたらと見づらい。修正したい時も、どこをいじればいいのかよく分からない（正解は「ファイルの変換」関数）。

こいつをすっきりまとめてみよう。まずは先ほどセルに表示させたフォルダパスを「ファイルパス」と名前定義してそこからクエリを作成。

    フォルダパス = Excel.CurrentWorkbook(){[Name="フォルダパス"]}[Content]{0}[Column1],
    ソース = 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.Last」を使うと、データ量によってはえげつなく重くなるので、リスト反転して先頭のテーブルを抜き出したほうがいい。それでも重くなる時は重くなるけど。