フォルダの結合のヘルパークエリを理解にかかることにした（１）

今年に入って，PowerQueryでフォルダ内部を結合しまくってる。

1つのシートの中に4つぐらいのフォルダを結合したものが別々に入ってくると，うっとおしくて仕方がない。特にこの補助クエリというやつのせいでクエリがかさんで仕方がない。

f:id:baruku07:20191215144700p:plain

フォルダを結合するときは相対パスを使う関係で，クエリ中の絶対パスを消しにかかるのだが，この書き換えをするときに元クエリだけでなく補助クエリまも書き換えが必要である（しかもそれに気付くのに相当かかった）。1個ならいいけど，4つあると2*4=8である。さらに，この補助クエリのせいでクエリのコピーで似た処理を複製することもままならない。

ということで，フォルダの結合のクエリをまじめに理解することにした。

まずヘルパークエリ以外のクエリはこんな感じである。

f:id:baruku07:20191215145346p:plain

ヘルパークエリが絡んでるのは囲んだ２か所である。キャプチャのソース全体。

let
    ソース = Folder.Files("（フォルダの絶対パスが入ってる）"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}, {"f", Int64.Type}, {"g", Int64.Type}, {"h", Int64.Type}})
in
    変更された型

結局は

ファイルの変換([Content]))　
#"サンプルファイル"

の2つがヘルパークエリーに絡んでいることがわかった。

結論を先にまとめとくと，

ファイルの変換([Content]))
各行のテーブルの実態を得るために使っている。Table.AddColumnの各行にこの関数を適用して，ほしいテーブルの実際を各行に対してくっつけていくために使っている。
#"サンプルファイル"
1.でくっつけたテーブルの実態をTable.ExpandTableColumnで展開するときに，引数として展開したい列を指定することが必要になる。それを得るために使っている。

ということがわかった。以下，１つずつ見ていく。

1. ファイルの変換

関数ファイルの変換の実態は次のようになっている。

let
    ソース = (パラメーター1) => let
        ソース = Excel.Workbook(パラメーター1, null, true),
        テーブル1_Table = ソース{[Item="テーブル1",Kind="Table"]}[Data]
    in
        テーブル1_Table
in
    ソース

1行目はExcel.Workbookにパラメーターを渡している。元のクエリの関数呼び出しを見ると，これはファイルのContentの行，すなわちファイルのバイナリを渡していることがわかる。

Folder.Files(絶対パス)で呼び出したテーブルの実態はこんな感じである。

f:id:baruku07:20191215150419p:plain

Excel.Workbookはバイナリを渡すと（ファイル名でない），その中身がExcelだった場合は，全部のシート・テーブル・範囲など使えそうなもの一覧で返してくれる関数である。レコードが返ると書いてあるがテーブルが返ってくるようである（見た目や後のアクセスの記法から推察して）

f:id:baruku07:20191215150938p:plain

PQを使い始めた最初，セルの中にバイナリの実態があるという考え方になじめずに戸惑ったのだがそういうものらしい。

で，そこから

ソース{[Item="テーブル1",Kind="Table"]}[Data]

として，得られたテーブルの中から指定したテーブル名と一致する（この場合はテーブル名「テーブル1」のものを引っ張ってきた）行を取り出して（{[Item="テーブル1",Kind="Table"]}），"[Data]"で実態を取り出すということになっている。

すなわちこの関数はExcelファイルの実態を与えて，指定したテーブルの中身を返す関数となっている。ただし，中身の指定は生の値で書かれていることに注意である。

この関数の呼ばれ方

ここで定義した関数はこんな感じで呼ばれている。

    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),

要は，Folder.Files()で読み込んだテーブルの各行に対して，ファイルの実態を渡してテーブルを返して，それを一列くっつけるということをやっているのである。関数適用後のテーブルを見ればこれはわかる。

f:id:baruku07:20191215151753p:plain

2.#"サンプルファイル"

各ファイルに対して中身くっつければそれでほぼ目的は達成のはずである。なぜわざわざ特定のファイルを持ち出してるのかがわからない。

このファイルを呼ばれている部分を見てみる。

   展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),

Table.ExpandTableColumnなんで，どうやら中身を展開していることはわかる。その関数の引数に" Table.ColumnNames(ファイルの変換(#"サンプルファイル")"がある，ということは自然に考えて展開する列名を取得していることがわかる。リファレンスをみても手動で展開してみてもそれで間違いなさそうである。

しかし，なんで全部の列名を展開するだけなのにわざわざ列名を取得する必要があるのか。よくわからないけど，これはそういうものらしい。全部を一気に展開するというのがみつからない（そんなのはないとまで書かれてるのもみかけた。ここは真偽はまだ確かめてない）。

1. ファイルの変換

この関数の呼ばれ方

2.#"サンプル ファイル"

2.#"サンプルファイル"