Power Query：複数のシートのデータを縦結合する（ヘルパークエリ編）

複数のブックを統合するのに標準機能でやると、出現するのがヘルパークエリなんだけども、これが苦手な人も多いのではないかと思う。でも慣れれば超便利なので、是非とも仕組みを理解して活用して欲しい。もちろん他の用途にも応用できる。

今回は「複数のシートを縦結合する」クエリをヘルパークエリで作ってみる。

複数シートのデータを縦結合

普通にやる方法は以前にやったので割愛。

最初に一点注意。対象のファイルとデータを読み込むファイルは必ず分けること。以上。

新規クエリに下記の順で作成していく。

参照ブック
let
    フォルダパス = "C:\……\……\", //ここにフォルダパスを入力
    ファイル名 = "対象ファイル.xlsx", //ここにファイル名を入力
    ソース = Folder.Files(フォルダパス),
    絞り込み = Table.Skip(ソース, each [Folder Path]<>フォルダパス),
    対象ブック = Excel.Workbook(Table.Skip(ソース, each [Name]<>ファイル名){0}[Content])
in
    対象ブック

ソースのところまでは「フォルダから」で指定するだけだからマウス操作でできる。今回、上から該当するファイルを絞り込みたかったので、参照にアイテムアクセスを使うのは避けた。もしいらないシートがあるなら、ここで取り除いておこう。

パラメータ1
"シート1" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

これも作るのは簡単。空の新規クエリを右クリックして「パラメータに変換」と押せば「meta」より後は勝手に作成される。「""」となっているところを先頭のシート名（今回は「シート1」）に変更して、「Type」を「Text」にしておけばいいので、これもほぼマウス操作だけでできる。

サンプルシートの変換
let
    ソース = 対象ブック,
    対象シート = ソース{[Item=パラメータ1, Kind="Sheet"]}[Data],
    ヘッダーに昇格 = Table.PromoteHeaders(対象シート)
in
    ヘッダーに昇格

これは「対象ブック」クエリを参照して必要な加工を入れただけ。クエリを作成したら、左ペインの「クエリ」で「サンプルシートの変換」クエリを右クリックして、「関数の作成」を選択する。

関数の作成

すると関数名を尋ねられるので、「シート読み込み」と入力して［OK］。

シート読み込み関数の作成

こうすることで「サンプルシートの変換」クエリを編集すれば、自動的に「シート読み込み関数」も内容が更新されるようになる。ここまではマウス操作と日本語入力だけでできる。

これでヘルパークエリの準備は完了。あとは新規のクエリで対象ブックを参照して加工するだけ。

シートの結合
let
    ソース = 対象ブック,
    Table列の追加 = Table.AddColumn(ソース, "Table", each シート読み込み([Item])),
    列の選択 = Table.SelectColumns(Table列の追加, {"Name", "Table"}),
    Table列の展開 = Table.ExpandTableColumn(列の選択, "Table", {"列1", "列2"}),
    列名の変更 = Table.RenameColumns(Table列の展開,{{"Name", "シート名"}})
in
    列名の変更

のようにすればおしまい。これもステップを見てもらえば分かるけど、マウス操作で問題なくできる。

「Table列の追加」はカスタム列の追加で「= シート読み込み([Item])」と入れただけだし。ステップ名だけ不細工だから変えてるけど。

シートの結合クエリの結果