【Power Query】フォルダ内データを自動取込みする設定

Power Queryでフォルダ内データを自動取込みする設定のアイキャッチ画像 Power Query

Power Queryでフォルダ内のデータを取り込む処理を、最初はCSVの中身をコード側でそのまま処理する作り方にしていました。動くには動きますが、元データを見返すたびにCSVを開き直す必要があり、CSV自体を更新する運用だと参照周りの管理も面倒になります。

そこで、VBAで処理する前段階として、Power Queryでデータの取り込み口を先に整えておく方法に切り替えました。フォルダにデータを置いておけば、あとは自動で最新のものを拾ってきます。今回はその設定手順をまとめます。

この手順で解決できること

  • 毎回手作業でファイルを開いて処理する手間をなくす
  • フォルダごと別の場所に移動しても、パス設定が壊れないようにする
  • CSVでもExcelファイルでも、同じ手順で最新データを取り込む
  • Power Query使用時に自動生成される「ヘルパークエリ」でクエリ一覧が煩雑になるのを避ける

前提知識・用語

Power Query
Excelに標準搭載されている、複数のファイルやデータを自動で取り込み・加工する機能です。一度クエリを作っておけば、「更新」ボタンひとつで最新のデータに置き換えられます。

クエリ
Power Queryで作る、データの取得・加工手順です。

M言語
クエリの中身を記述する言語です。通常はPower Query画面のマウス操作でこのコードが自動生成されますが、今回は画面操作の代わりにコードを直接貼り付けます。

詳細エディター
M言語のコードを直接編集できる画面です。Power Queryエディターの「ホーム」タブから開けます。

ヘルパークエリ
フォルダやファイルを取り込む操作をすると自動的に作られる、「サンプルファイル」「パラメーター1」などの補助クエリです。増えるとクエリ一覧が分かりにくくなるため、今回の手順では作りません。

相対パス
ブック自身の場所を基準にしたファイルの位置指定方法です。フォルダごと移動しても指定し直す必要がありません。対義語は「絶対パス」（C:\Users\…のような固定された場所）です。

全体の流れ

①フォルダ準備 → ②setupテーブル作成 → ③空クエリにコード貼付 → ④4か所を書き換え → ⑤完了、以後は更新のみ

Step1：フォルダとファイルを準備する

次の構成でファイルとフォルダを配置します。

［親フォルダ］（任意の場所でOK）
├── 処理用ファイル.xlsx  ← 本手順を作成・実行するブック
└── ［データ格納フォルダ］ ← 例：売上データ、月次データ など
      ├── データ1.csv
      └── データ2.csv

ポイント：「処理用ファイル.xlsx」は、必ず一度「名前を付けて保存」をしてから作業してください。保存前（タイトルバーが「Book1」などの状態）だと、次のStepでブックの場所を自動取得できません。

取り込み元のファイルがZIPで届く場合は、先に解凍してからフォルダに置きます。VBAで解凍し、完了を確認する方法は、【VBA】ZIP解凍の完了確認｜CopyHereの戻りを実測にまとめています。

Step2：setupテーブルの作成

Power Query側から「このブックが今どこに保存されているか」を参照できるように、目印となる小さな表をブックの中に作ります。

  1. ブックに新規シートを追加し、シート名を「設定」に変更する
  2. 「設定」シートのA1セルに次の数式を入力する。このブックが保存されているフォルダのパスを文字列として取り出す数式で、保存場所が変わっても結果は自動的に更新される
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
  1. A1セルを選択した状態で「Ctrl + T」を押し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」をクリックする
  2. A1セルの見出し名を「path」に変更する
  3. 「テーブルデザイン」タブの「テーブル名」を「setup」に変更する

ポイント：名前は「setup」「path」に正確に合わせてください。ここがずれると、後で貼り付けるコードから参照できずエラーになります。半角・大文字小文字も区別されます。

設定シートに作成したsetupテーブル。A1セルにpath、A2セルに保存先フォルダのパスを取得する数式を入れている

Step3：Power Queryのクエリを作成する

  1. 「データ」タブ→「データの取得」→「その他のデータソースから」→「空のクエリ」をクリックする
  2. Power Queryエディターが開いたら、「ホーム」タブの「詳細エディター」をクリックする
  3. 詳細エディター内の既存コードをすべて削除し、Step4のテンプレートコードを貼り付ける
  4. Step5を参照して、自分の環境に合わせて4か所を書き換える
  5. 「完了」をクリックし、データに問題がなければ「閉じて読み込む」をクリックする
詳細エディターに貼り付けたPower Queryのテンプレートコード全文。構文エラーが検出されていない状態

「完了」をクリックすると、取り込んだデータのプレビューと、これまでの処理が「適用したステップ」として一覧表示されます。

Power Queryエディターで完了直後に表示される、取り込まれたデータのプレビューと適用したステップの一覧

「閉じて読み込む」を押すと、ワークシート上にテーブルとして取り込み結果が展開されます。

閉じて読み込む後、ワークシートに展開された取り込み結果のテーブル

Step4：テンプレートコードの中身を理解する

以下がStep3で貼り付けるコードの全文です。長く見えますが、6つのブロックに分けて、それぞれ何をしているかを順番に説明します。

テンプレートコード全文

let
 // 1. 作成済みの「setup」テーブルの「path」列から1行目の値（フォルダパス）を取得
 CurrentPath = Excel.CurrentWorkbook(){[Name="setup"]}[Content]{0}[path],
 TargetFolder = CurrentPath & "データ格納フォルダ", //★環境に合わせて変更★
 // 2. フォルダ内のファイルを取得して最新【指定件数】に絞り込み
 ソース = Folder.Files(TargetFolder),
 並べ替えられた行 = Table.Sort(ソース,{{"Date modified", Order.Descending}}),
 保存された先頭行 = Table.FirstN(並べ替えられた行, 1), //★環境に合わせて変更★
 フィルター選択された非表示のFile1 = Table.SelectRows(保存された先頭行, each [Attributes]?[Hidden]? <> true),
 // 3. 拡張子（CSVかExcelか）を自動判別してインラインで展開
 データの展開処理 = Table.AddColumn(フィルター選択された非表示のFile1, "データの展開", each
 let
  Extension = Text.Lower([Extension]),
  // 3. CSVの場合の読み込み
  CsvData = if Extension = ".csv" then Csv.Document([Content], [Delimiter=",", Columns=null, Encoding=932, QuoteStyle=QuoteStyle.None]) else null,
  CsvPromoted = if CsvData <> null then Table.PromoteHeaders(CsvData, [PromoteAllScalarTypes=true]) else null,
  // 4. Excelの場合：ブック内の全シートを結合して読み込み
  ExcelSheets = if Extension <> ".csv" then Table.SelectRows(Excel.Workbook([Content], true), each [Kind] = "Sheet") else null,
  ExcelCombined = if ExcelSheets <> null then Table.Combine(ExcelSheets[Data]) else null,
  Result = if Extension = ".csv" then CsvPromoted else ExcelCombined
 in
  Result
 ),
 // 5. 不要な列を削除し、展開
 削除された他の列1 = Table.SelectColumns(データの展開処理, {"データの展開"}),
 列名のリスト = Table.ColumnNames(削除された他の列1{0}[データの展開]),
 展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "データの展開", 列名のリスト),
 // 6. 列名を固定せず、展開されたすべての列に対して動的に型を「テキスト型」に変換
 現在のすべての列名 = Table.ColumnNames(展開されたテーブル列1),
 動的な型指定 = List.Transform(現在のすべての列名, each {_, type text}),
 動的な型の変換 = Table.TransformColumnTypes(展開されたテーブル列1, 動的な型指定)
in
 動的な型の変換

各ブロックの解説

①パスを取得する部分
Step2で作った「setup」テーブルからpath列（1行目）の値を取り出しています。数式で自動計算した「ブックの保存場所」を、ここで呼び出しています。

②対象ファイルを絞り込む部分
対象フォルダの中身をファイル一覧として取得します（Power Query公式のFolderコネクタのリファレンス）。更新日時が新しい順に並べ替え、指定した件数だけ取り出します。隠しファイルは対象から除外されます。

③CSVの場合：読み込む部分
拡張子が「.csv」であれば、指定した文字コード・区切り文字でファイルを読み込み、1行目を見出し行として昇格します。

④Excelの場合：全シートを結合する部分
拡張子がCSV以外、つまりExcelファイルであれば、ブックの中身を取得します。「Kind」が「Sheet」のもの（実際のワークシート。名前付き範囲やテーブル定義などは除く）だけに絞り込み、すべてのシートのデータをTable.Combineで1つの表として結合します。同じ表形式のデータが複数シートに分かれているExcelファイルでも、シート数に関係なく1つの表にまとめられます。

ポイント：この結合処理は、各シートとも1行目が見出し行であることを前提にしています。Excel.Workbook([Content], true) の第2引数trueが、各シートの1行目を見出し行として扱う指定です。見出し行の位置がずれているシートがある場合は、事前にシート側を整えるか、この部分を調整してください。

⑤表を実際の列として展開する部分
③④で取り出したデータは、まだ表の中に表が入っている状態です。最初のファイルの列名を読み取り、それを使って実際の列（日付、金額など）として展開します。

⑥すべての列をテキスト型に統一する部分
列名を個別に指定せず、展開されたすべての列をまとめて「テキスト型」に変換しています。元データの列が増減したり列名が変わったりしても、クエリの更新がエラーで止まりにくくなります。

ポイント：数値や日付として計算に使う場合は、この後に「型の変換」のステップをもう1つ追加してください。今回のテンプレートは「まずエラーなく取り込む」ことを優先した設計です。

Step5：4か所を書き換える

以下の4か所を自分の環境に合わせて書き換えれば動作します。

該当箇所テンプレート内の記述設定内容・書き換え例
①参照先フォルダ名（3行目）“データ格納フォルダ”【必須】親フォルダ内にある対象フォルダ名に変更。例：”売上データ”や”月次データ”
②取得ファイル件数（8行目）1取得したいファイルの件数を指定。最新1件のみ→1／過去3ヶ月分など複数結合→3
③文字コード設定（15行目）Encoding=932標準（Shift-JIS）→932／文字化けする場合（UTF-8）→65001
④区切り文字（15行目）Delimiter=”,”カンマ区切り（CSV）→”,”／タブ区切り（TSV）→”#(tab)”

うまくいかないときは

症状・エラー考えられる原因対処法
「フィールドが見つかりません：’setup’」setupテーブルの名前、またはpath列の見出し名が指定と違う「設定」シートのテーブル名・見出し名を確認し、半角・大文字小文字まで正確に合わせる
「アクセスが拒否されました」（”Access to the resource is forbidden”）対象フォルダやファイルへのアクセス権がない共有フォルダのアクセス権設定を確認する
データが文字化けするCSVの文字コードがShift-JIS（932）以外（UTF-8など）テンプレートのEncoding=932をEncoding=65001に書き換える
「パスが見つかりません」setupの数式を作った時点でブックが未保存だったブックを一度保存し直し、「設定」シートのA1セルを再計算（F9）する
取り込んだデータの列がずれる／足りないフォルダ内のファイルによって列の並びや列数が異なる対象フォルダ内のファイル形式を統一するか、展開後に列の調整ステップを追加する
Excelファイルの列がシートによってバラバラになる／余分な列が増える各シートの見出し行の位置や列名が統一されていない元のExcelファイル側でシート構成（見出し行の位置・列名）を揃える。Step4の④のポイントも参照

このやり方のメリット

  • フォルダ移動に強い（相対パス）：親フォルダごとデスクトップ・共有フォルダ・別端末へ移動しても、パス切れエラーが発生しません。
  • クエリ一覧が汚れない（1クエリ完結）：自動生成される「ヘルパークエリ」や「サンプルファイル」が作られないため、メンテナンスが容易です。
  • 列構成の変化によるエラーを防ぐ：元データの列が増減したり名前が変わったりしても、すべての列を動的に「テキスト型」として展開するため、クエリの更新処理が途中で止まりません。
  • Excelの複数シートにも対応：同じ表形式のデータが1つのExcelファイルの中で複数シートに分かれていても、ブック内の全シートを自動結合して1つの表として取り込めます。

他の人が作ったブックのクエリを引き継ぐ場合は、まずクエリの一覧と読み込み先を確認します。接続専用のクエリの見分け方やVBAでの一覧の出し方は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続にまとめています。

溜めたCSVのうち、年月ごとに作成日時が最新の版だけを取り込む書き方は、【Power Query】年月ごとにCSVの最新版だけを採用するで扱っています。

まとめ

今回はVBA側の処理には触れていません。ただ、この入り口整備を挟むようになってから、後段のマクロの安定度が上がりました。次回以降、VBA側の処理についても書いていく予定です。

コメント

タイトルとURLをコピーしました