Power Query でフォルダから複数ファイルを一括インポートする

 Power Query で,あるフォルダ内の同一構造のファイルを一括してインポートする機会は多い.M 言語は未開拓であるが,その一端に触れてみた.

 EXCEL ブックであれ csv ファイルであれ,構造化されたデータという観点から見れば,ファイル形式などどうでも良い話である.この抽象化が理解できれば,Power Query への理解が一定程度進むのではないかと思う.

 この記事はEXCEL VBA でフォルダ内のブックを開きデータを読み込むと対応する.

まず「フォルダーパスの指定」

 EXCEL であれ Power BI であれ,Power Query エディタの「データの取得」「ファイル」「フォルダ」でフォルダーパスをまず指定する.

EXCEL での「データの取得」「ファイルから」「フォルダから」
EXCEL での「データの取得」「ファイルから」「フォルダーから」
Power BI で「データを取得」「ファイル」「フォルダ」
Power BI での「データを取得」「ファイル」「フォルダー」

何はともあれ「データの変換」

 プレビューでは「データの変換」へと進む.Content, Name, Extension, Date accessed, Date modified, Date created, Attributes, Folder Path の8列がプレビューで表示されている.

Name にフィルターをかける

 同一構造のファイルは,ファイル名にも共通点があることが多い.その共通点を含むように,テキストフィルターをかける.その共通点は各自のシステムによって異なるだろうが,何らかの特徴があるはずだ.

M 言語への入り口は「列の追加」

 Power Query エディタの最上部に「列の追加」タブがある.「カスタム列」を選ぶ.「カスタム列の式」が M 言語の関数である.「新しい列名」は次の処理ステップで使われる変数名となり,初期値は「カスタム」である.

データ関数はテーブルを返す

 ここでソースファイルが csv ファイルか EXCEL のブックかで関数の記述は異なるが,得られる結果はほぼ共通している.ソースファイルが csv ファイルなら  csv とタイプするとインテリセンスが働いて自動的に次の候補がポップアップしてくる.この場合 Csv.Document にカーソルが乗った状態でリターンキーを押下する. Excel. とタイプすると Excel.Workbook と続く.他にも Excel.CurrentWorkbook というデータ関数もあるが,違いがよく分からない.

 記事公開後,相違について教えてくれた方がいた.感謝したい.

 これらの関数はデータ関数と呼び,テーブルを返す.関数の種類は数十にも及び,「データの取得」ユーザーインターフェースに使われているデータソースはほぼここから使われている.

csv.Document を選ぶ
Csv.Document を選ぶ

 さらに ([ とタイプすると,次の候補がポップアップする.ここでもやはり Attributions, Content, Date accessed, Date created, Date modified, Extension, Folder Path, Name が候補となる.先程フォルダーパスを指定した直後と同様である.

ソースファイル内部の候補がポップアップする
ソースファイル内部の候補がポップアップする

 ここで矢印キーで [Content] にカーソルを載せてリターンキーを押下すると Csv.Document([Content] まで入力されるので, ) とタイプして関数を閉じる.構文エラーチェックが通ったのを確認して OK をクリックする.

「カスタム列」以外は削除

 必要なのはファイル内のデータであるから,「カスタム列」の Table 以外は不要で削除する.

カスタム列の Table 以外は削除
カスタム列の Table 以外は削除

テーブルの展開

 カスタム列の右肩に展開アイコンがあるので,クリックする.「展開」「集計」では「展開」がチェックされており,そのまま OK をクリックする.ここで「集計」をチェックするとどうなるかはまだ確認していない.

「展開」「集計」では「展開」がチェックされており,そのままOKをクリックする
「展開」「集計」では「展開」がチェックされており,そのままOKをクリックする
Power BI でのカスタム列の展開
Power BI でのカスタム列の展開

 この後の作業は「1行目をヘッダーとして使用」「列のピボット解除」である.

2件目以降のヘッダーを除外するのを忘れない

 忘れがちであるが,複数のファイルを一括インポートすると,2件目以降のファイルの1行目が途中途中で出現する.これをフィルターで除外するのを忘れると,「データ型の変換」あたりか,最後の「閉じて読み込む」あたりで躓くことになる.

ヘッダー行だけをフィルターで選択した状態
ヘッダー行だけをフィルターで選択した状態

課題は PDF

 csv ファイルと EXCEL のブックの一括インポートは手順はほぼ同じであるが,PDF ファイルからのインポートが未解決である.Power BI と EXCEL で仕様が異なるらしい.EXCEL だと「その他のソース」「空のクエリ」で指定できるらしいが,まだ取り組めていない.Pdf.Tables 関数は .NET 4.5 が必要なため使用不可となっている.Binary 関数を使用すれば開けるのかも知れない.今後の課題である.

.xls 形式に注意

 その後 .xls 形式のファイルをフォルダーごとインポートする機会があったのだが,クエリの検証にやたらと時間がかかる現象が発生した.どうやら古い形式のファイルの取得には時間がかかるものらしい.

 この問題はいささかトリッキーな方法で回避した.つまり,別のフォルダをソースとして用意しておき,そこに実ファイルを少しコピーしておいてクエリを記述し,最後に読み込む直前にフォルダーパスを本来のパスに書き換える,というものである.

 確かに時間はかかるが,クエリのステップごとに検証に時間を取られるよりはマシである..xls 形式の場合は VBA で記述してしまったほうが早いのかもしれない.

One Reply to “Power Query でフォルダから複数ファイルを一括インポートする”

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください