複数フォルダ内のExcelのテーブルを1テーブルに集約する #Excel

対象の要件

複数のフォルダに存在する、複数のExcelブックの内容を集約したい
それらのブックの集約対象は全て同じ名前、同じ列名の「テーブル」を持っている

適用対象外

フォルダ内によけいなフォルダやファイルがある
紹介するクエリの中でフィルタリングすれば簡単だが割愛する。
ファイル名の一部に区分が書いてあるので値として使用したい
抽出に正規表現必須とか言わなければ比較的容易だが割愛する。
「テーブル」でなくただの範囲
参考になる記事 Power Queryで複数のExcelブックの共通しない名称のシートを読み込む - Qiita がある。流用すれば複数のフォルダに対応できそう。
ただの範囲だし、開始位置も終了位置もわからないし、範囲外に不要な文字列がある
VBAの出番と思われる。フォーマットが整ってはいるが残念ながらExcel方眼紙なブックは大量に存在するので Power Query で捌けないか興味はある。

サンプルの前提

下記のようなブックを複数、複数のフォルダに配置する。

ブック名：任意
シート名：任意
テーブル位置：任意
テーブル名： theTable
列名： c1, c2, c3

得られる結果

以降、すっとばしの手順とステップバイステップの手順を紹介するが、いずれも下図の結果が得られる。

事前準備

集約する前に、集約用ブックに対象のフォルダを記入してテーブル化しておく。
（クエリにこれを参照させ、対象フォルダが増減したときにコードを変更する手間をなくすため）

テーブル名：paramTable
列名：folderPath
ほか任意

すっとばしの手順

詳細エディターを開く
1. Excel - データ＞データの取得＞その他のデータソースから＞空のクエリ
2. Power Query エディター - ホーム＞詳細エディター

詳細エディター内のコードを上書きする

これまでの前提・事前準備に沿った命名になっているためそれぞれ読み替えること。

  let
      #"ソース" = Excel.CurrentWorkbook(){[Name = "paramTable"]}[Content],
      #"フォルダ内のファイル" =
          Table.AddColumn(
              #"ソース",
              "files",
              each Folder.Files([folderPath])
          ),
      #"展開されたfiles" =
          Table.ExpandTableColumn(
              #"フォルダ内のファイル",
              "files",
              {
                  "Content",
                  "Name",
                  "Extension",
                  "Date accessed",
                  "Date modified",
                  "Date created",
                  "Attributes",
                  "Folder Path"
              },
              {
                  "file.Content",
                  "file.Name",
                  "file.Extension",
                  "file.Date accessed",
                  "file.Date modified",
                  "file.Date created",
                  "file.Attributes",
                  "file.Folder Path"
              }
          ),
      #"追加されたカスタム" =
          Table.AddColumn(
              #"展開されたfiles",
              "カスタム",
              each
                  Excel.Workbook(
                      [file.Content],
                      null,
                      true
                  ){[
                      Item = "theTable",
                      Kind = "Table"
                  ]}
                      [Data]
          ),
      #"展開されたカスタム" =
          Table.ExpandTableColumn(
              #"追加されたカスタム",
              "カスタム",
              {
                  "c1",
                  "c2",
                  "c3"
              },
              {
                  "theTable.c1",
                  "theTable.c2",
                  "theTable.c3"
              }
          ),
      #"区切り記号の後の抽出されたテキスト" =
          Table.TransformColumns(
              #"展開されたカスタム",
              {
                  {
                      "folderPath",
                      each
                          Text.AfterDelimiter(
                              _,
                              "\",
                              {
                                  1,
                                  RelativePosition.FromEnd
                              }
                          ),
                      type text
                  }
              }
          ),
      #"削除された列" =
          Table.RemoveColumns(
              #"区切り記号の後の抽出されたテキスト",
              {
                  "file.Content",
                  "file.Extension",
                  "file.Date accessed",
                  "file.Date modified",
                  "file.Date created",
                  "file.Attributes",
                  "file.Folder Path"
              }
          )
  in
      #"削除された列"

「区切り記号の後…」以降は装飾のための操作のためなくても問題はない

完了、閉じて読み込む

ステップバイステップ

テーブル paramTable を起点にクエリを作成する
フォルダパスをファイルリストに展開して各ファイル内のテーブルを展開して、という流れのため、このテーブルが起点になる。
1. テーブル paramTable 内の適当なセルを選択する
2. Excel - データ＞データの取得＞その他のデータソースから＞テーブルまたは範囲から
不要なステップを削除する
1. ステップ 変更された型 は今回は不要なので×ボタンで消す
folderPath 列のパスをファイルリストに変換した列を追加する
1. 列の追加＞カスタム列＞列の追加
2. 列名： files
3. 式： Folder.Files([folderPath])
  式の意味：指定フォルダー内のファイルを取得する。ファイルの各種プロパティをレコードとしたテーブルの形をとる。
4. 完了
5. ステップ名を設定する
  1. ステップを選択して F2
  2. フォルダ内のファイル に変更して Enter
  3. ※「完了」以降の手順は以降では省略する。デフォルトでわかりにくければ適宜リネームすること。
files 列のファイル情報テーブルを展開する
1. プレビュー内で files 列の適当なセルを選択
2. 変換＞構造化列＞展開
3. 列名のプレフィックス： file
4. 使わない列はチェックボックスで外してもよいが、後から追加すると後続のステップを壊す場合がある。また、ステップが多くなると読みにくくなるため、序盤はロジックに専念し、必要な情報が出揃ってから整形と合わせて列削除をするようにステップの棲み分けをすると、読みやすくなる。(メモリに優しくないかも知れないが未検証)
file.Content 列のファイルデータから theTable テーブルを抽出した列を追加する
1. 列の追加＞カスタム列＞列の追加
2. 列名： カスタム (考えてなかったのでデフォルトになった)
3. 式：
```
  Excel.Workbook(
      [file.Content],
      null,
      true
  ){[
      Item = "theTable",
      Kind = "Table"
  ]}
      [Data]
```
式の意味： Excel.Workbook() でバイナリからブックを開き、{...} で対象テーブルを取得、そのデータを得る。
カスタム 列の theTable テーブルを展開する
1. プレビュー内で カスタム 列の適当なセルを選択
2. 変換＞構造化列＞展開
3. 列名のプレフィックス： theTable
できあがり
1. 長い文字列のカットや不要な列の削除などを以降のステップで行う
2. ホーム＞閉じる＞閉じて読み込む