（この記事は、2021/12/08に執筆し、2022/1/29に修正、追記しました）
フォルダー内のファイルのデータを取得するには、「データを取得」からフォルダーを選ぶと思いますが、この操作を行うとヘルパークエリなどが勝手に出来上がってしまいます。このヘルパークエリの中身については、別途考えるとして、Power Queryを直接書いて操作を行っていきたいと思います。
また、画面操作で項目の項目の展開を行うと、後で項目が増えたときにコードの書き換えをしなければなりません。項目がどれだけあるか気にせずにまとめる方法を説明します。
パラメータを作成
まず最初に、フォルダーの位置をパラメータ "folder" に設定しておきます。後で変更になりそうな値は、コード中に直接書き込まずに、パラメータを使ってコードの外に出しておきましょう。
PCに詳しい方は、環境変数を使う方法を思いつくと思いますが、Power QueryでPCの環境変数を参照することはできません。私もかつて数時間かけて調べましたが、Power BIサービスに発行したときに、意味のない操作になると気づいて諦めました。また、セキュリティ面からも、そのようなデータへのアクセス方法は望ましくありません。
シートのデータだけをまとめる Table.Combine
ブック名やシート名が必要なく、データだけササッとほしいという場合は、Table.Combileでまとめてしまいましょう。
Folder.Files
フォルダー内のファイルを取得するには、Folder.Filesを使用します。
let
Source = Folder.Files(folder)
in
Source
Excel.Workbookでエクセルデータソースへアクセス
Binaryをエクセルファイルとして読めるようにします。エクセルの表の１行目を項目名にするので、Excel.Workbookの第２引数は true にしておきます。
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Source,
{
"Content",
each
Excel.Workbook(_, true)
}
)
シートをばらす
Table.ExpandTableColumnを使って、シートのデータが入っているDataを取り出します。
// シートをばらす
Expand =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Data"},
{"Data"}
)
各シートのデータを１つの表にまとめる
Table.Combineを使うと、項目名を指定することなく、表を結合することができます。
// 各シートのデータを結合
Combine = Table.Combine(Expand[Data])
まとめ
コードをすべてまとめると以下のようになります。
let
Source = Folder.Files(folder),
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Source,
{
"Content",
each
Excel.Workbook(_, true)
}
),
// シートをばらす
Expand =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Data"},
{"Data"}
),
// 各シートのデータを結合
Combine = Table.Combine(Expand[Data])
in
Combine
ファイル名、シート名を残してシートの項目をまとめる１ Table.ExpandTableColumn
Folder.Files
前と同じように、フォルダー内のファイルを取得するには、Folder.Filesを使用します。
let
Source = Folder.Files(folder)
in
Source
項目の整理
ContentのBinaryの中に、エクセルファイルが入っています。ここでは、ContentとNameだけを残して、あとは削除します。また、NameはFileNameに変更し、項目の並べ替えも行います。
// Content(エクセルデータ)とName(ファイル名)以外を削除
RemoveOtherColumns =
Table.SelectColumns(
Source,
{"Content", "Name"}
),
// NameをFileNameに変更
Rename = Table.RenameColumns(
RemovedOtherColumns,
{"Name", "FileName"}
),
// ファイル名を１番目に移動
Reorder = Table.ReorderColumns(
Rename,
{
"FileName",
"Content"
}
)
Excel.Workbookでエクセルデータソースへアクセス
Contentにあるエクセルファイルを開いていきます。エクセルの表の１行目を項目名にするので、Excel.Workbookの第２引数は true にしておきます。
Workbook =
Table.TransformColumns(
Reorder,
{
"Content",
each Excel.Workbook(_, true)
}
)
ワークシート展開 ExpandTableColumnとPromoteHeaders
ワークシートの項目を展開すると、以下のようになります。
let
・・・ここは前と同じ・・・・
ExpandSheets =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Name", "Data"},
{"SheetName", "Data"}
)
in
ExpandSheets
※特定のシート名のみを対象にしたい場合は、この後にSheetNameにフィルターをかける処理を挟みます。
通常のマウス操作で展開すると融通が利かない
このDataをそのまま展開すると以下のようになります。
let
・・・ここは前と同じ・・・・
#"Expanded {0}" =
Table.ExpandTableColumn(
ExpandSheets,
"Data",
{
"col1",
"col2"
},
{
"col1",
"col2"
}
)
in
#"Expanded {0}"
エクセルデータには、col3やcol4もありますが、最初に読み込んだものにはcol1とcol2しかなかったため、他の列は展開されませんでした。展開する前に、「さらに読み込み...」としてやれば、col3、col4も展開してくれますが、項目が増えた場合、その都度この操作をやりなおさなければなりません。
項目名を指定せず全て展開するには、次のようにコードを書きなおします。
全ての項目名を足したリストを使った結合
ブック名やシート名を残しておきたい場合は、Table.ExpandTableColumnでDataを展開します。その際、展開する項目のリストを作成して展開します。
Expand =
Table.ExpandTableColumn(
ExpandSheets,
"Data",
List.Union( // 全ての表の項目名の和集合を作成
List.Transform( // 各表の項目名のリストを返す
ExpandSheets[Data],
each Table.ColumnNames(_)
)
)
)
まとめ
let
Source = Folder.Files(folder),
// ファイル名とデータ項目を取得
RemovedOtherColumns =
Table.SelectColumns(
Source,
{"Content", "Name"}
),
// NameをFileNameに変更
Rename = Table.RenameColumns(
RemovedOtherColumns,
{"Name", "FileName"}
),
// FileNameを１番目に移動
Reorder = Table.ReorderColumns(
Rename,
{
"FileName",
"Content"
}
),
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Reorder,
{
"Content",
each Excel.Workbook(_, true)
}
),
// Contentからシート名とシートデータを取得
ExpandSheets =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Name", "Data"},
{"SheetName", "Data"}
),
// シートデータを結合
Expand =
Table.ExpandTableColumn(
ExpandSheets,
"Data",
List.Union( // 個別のリスト項目の和集合を作成
List.Transform( // 変換関数をリストに適用して新しいリストを返す
ExpandSheets[Data],
each Table.ColumnNames(_)
)
)
)
in
Expand
ファイル名、シート名を残してシートの項目をまとめる２ Table.AddColumnでファイル名を入れ込む
CurbalのYoutubeチャンネルで、ファイル名をAddColumnで埋め込んでいく方法が解説してありました。
ちょっとトリッキーかと思いましたが、その手法を使ってやってみました。
let
Source = Folder.Files(folder),
// Excelデータソースへのアクセス
Step1 = Table.TransformColumns(
Source,
{
"Content",
each Excel.Workbook(_, true)
}
),
// Contentにファイル名を埋め込む
Step2 = Table.AddColumn(
Step1,
"Workbooks",
(x)=>
Table.AddColumn(
x[Content],
"File",
each x[Name]
)
),
// ファイル名が埋め込まれたテーブルを結合
Step3 = Table.Combine(
Step2[Workbooks]
),
// Dataにファイル名、シート名を埋め込む
Step4 = Table.AddColumn(
Step3,
"Worksheets",
(x)=>
Table.AddColumn(
x[Data],
"File Sheet",
each [File = x[File], Sheet = x[Name]]
)
),
// ファイル名、シート名が埋め込まれたテーブルを結合
Step5 = Table.Combine(Step4[Worksheets]),
// ファイル名、シート名を展開
Step6 = Table.ExpandRecordColumn(
Step5,
"File Sheet",
{"File", "Sheet"}
)
in
Step6
Step2のところで、追加されたWorkbooksのテーブルの中にファイル名を埋め込んでます。
それをすべて結合すると、
Dataの中に、シートに登録されたデータがあるので、この中にファイル名とシート名を埋め込みます。別々にやるとスマートじゃないので、Step4では、レコード型で埋め込んでいます。
それを、Step5で全て結合し、Step6でStep4で埋め込んだファイル名、シート名をばらしています。
Step2とStep4で行った、外側の値を内側のテーブルに埋め込む方法ですが、Curbalでは以下のような書き方になっていました。
// Contentにファイル名を埋め込む
Step2 = Table.AddColumn(
Step1,
"Workbooks",
each
Table.AddColumn(
[Content],
"File",
(x)=> [Name]
)
),
// Dataにファイル名、シート名を埋め込む
Step4 = Table.AddColumn(
Step3,
"Worksheets",
each
Table.AddColumn(
[Data],
"File Sheet",
(x)=> [File = [File], Sheet = [Name]]
)
),
これだと、なにをやってるのかわかりにくいので、以下のように書き換えてみました。
// Contentにファイル名を埋め込む
Step2 = Table.AddColumn(
Step1,
"Workbooks",
(x)=>
Table.AddColumn(
x[Content],
"File",
each x[Name]
)
),
外側のTable.AddColumnのカラム・ジェネレーターで作成された値をxで受け、内側のTable.AddColumnのカラム・ジェネレーターで使っています。xには、ファイル名他の外側の列の値がレコードで入っているので、内側のテーブルにファイル名の列を作成することができます。
つまり、Curbalの方法で書かれたコードを更に冗長に書くと以下のようになります。
// Contentにファイル名を埋め込む
Step2 = Table.AddColumn(
Step1,
"Workbooks",
each
Table.AddColumn(
_[Content],
"File",
(x)=> _[Name]
)
),
外側のTable.AddColumnのカラム・ジェネレーターはeachになっているので、レコードの値は _ に入ります。これは省略可能なので _[Content] の代わりに [Content] と書くことができます。内側のTable.AddColumnのカラム・ジェネレーターでeachを使うと、_ の値が変わってしまうので (x) （カッコの中の変数はなんでもよい）を使って違う変数に値を入れ、_[Name] で外側のファイル名を内側で使っています。
参考
- MYNDA TREACY , Import Multiple Files Containing Multiple Sheets with Power Query, 2020.04.02, my Onlinetraining hub, https://www.myonlinetraininghub.com/import-multiple-files-containing-multiple-sheets-with-power-query
- しょうた, Excel PowerQuery 列の展開を全てまとめて実施する方法, 2021.08.10, Excel Excellent Technics, https://excel-excellent-technics.com/excel-powerquery-table-expandtablecolumn-11572