Power Queryの更新が遅くなる原因は、1つとは限りません。読み込む量が多い、同じ処理が何度も繰り返されている、フォルダ内のファイルが増え続けている、プライバシーの設定が影響している、といった要因が重なることもあります。
思いついた設定を次々に変えると、どれが効いたのか分からなくなります。この記事では、1回に1か所だけ変え、変える前と後で時間を測るという進め方で、確認する順番をまとめます。
Excelのバージョンによって、メニューの位置や名前が違うことがあります。手元の画面に合わせて読み替えてください。
準備：変更前の時間を記録する
測り方
Excelの画面には、更新にかかった時間を表示する欄がないことが多いため、ストップウォッチ（スマートフォンの時計アプリなど）で測れば十分です。測るときは条件をそろえます。
- 「すべて更新」と、特定のクエリだけの更新を分けて測る
- 同じ条件で2〜3回測り、ばらつきを見る（1回目だけ遅いこともある）
- ほかの重いブックやアプリは閉じておく
バックグラウンド更新が有効だと、更新中もExcelを操作できる代わりに、いつ更新が終わったのか分かりにくくなります。測る間だけ、クエリのプロパティで「バックグラウンドで更新する」をオフにしておきましょう。更新が終わるまで操作できなくなるので、終わったタイミングがはっきりします。
記録表
| 日付 | 変更した箇所 | 対象 | 1回目 | 2回目 | 3回目 | メモ |
|---|---|---|---|---|---|---|
| 変更前（基準） | すべて更新 | |||||
| 不要な列を削除 | Q_売上明細 |
表には自分の環境で測った数字を入れます。速くならなかった変更を元に戻すかどうかも、この表を見て決めます。
確認1：どのクエリが遅いのかを特定する
「すべて更新」が遅くても、実際に時間がかかっているのは1つのクエリだけ、ということがあります。
- 「データ」タブから「クエリと接続」ウィンドウを開く
- クエリを1つずつ右クリックして「更新」を実行し、時間を測る
- 一番時間がかかったクエリから、以下の確認を進める
ピボットテーブルやデータモデルを使っている場合は、それらの更新も「すべて更新」に含まれます。クエリ単体では速いのに全体が遅いなら、クエリ以外の更新もあわせて確認します。
確認2：読み込む行・列を減らす
最初に見るのは、必要以上のデータを扱っていないかです。
列はできるだけ早い段階で絞る
- 使わない列は、ステップのなるべく前のほうで削除する
- 「列の削除」より「列の選択」で必要な列だけを残すほうが、元データに列が増えたときに影響を受けにくい
行もできるだけ早い段階で絞る
- 期間や区分で絞り込むフィルターは、結合・グループ化・列の追加より前に置く
- 過去の年度の分が不要なら、取り込み元の段階で除いておく
読み込み先を見直す
クエリの結果をすべてシートに出していると、行が多いほどシートへの書き出しに時間がかかります。
- 途中の加工用のクエリは「接続専用」にする（「読み込み先」で「接続の作成のみ」を選ぶ）
- シートに出す必要がなく集計に使うだけなら、データモデルに読み込む方法や、ピボットテーブルの元データとして使う方法も検討する
ステップの順番を入れ替えたら、結果の行数や合計が変わっていないかも確認します。フィルターを前に移したことで、結合の結果が変わる場合があるためです。
確認3：参照やマージで同じ処理が繰り返されていないか
あるクエリ（A）を参照して別のクエリ（BとC）を作ると、更新のたびに、BとCそれぞれでAの処理が実行されることがあります。Aを「接続専用」にしても、Aの結果が保存されて使い回されるとは限りません。
見つけ方
- Power Queryエディターの「表示」タブにある「クエリの依存関係」で、どのクエリがどのクエリを参照しているかを確認する
- 1つの重いクエリから、多くのクエリが枝分かれしていないか
- マージ（結合）の相手として、同じ重いクエリを何度も使っていないか
対処の方向
- 参照されるクエリ（A）の段階で、行と列を十分に減らしておく
- 似た加工を別々のクエリに分けているなら、1つのクエリにまとめられないか検討する
- マージする前に、結合相手のクエリを必要な列とキーだけに絞る
- マージ後に展開する列は、必要なものだけにする
どのクエリを変えたかは、記録表に残しておくと効果を比べやすくなります。参照したクエリの結果をメモリに保持するTable.Bufferという関数もありますが、使いどころに注意が必要なため、この記事では扱いません。
確認4：フォルダ結合で読み込むファイルの数
フォルダ内のCSVやExcelをまとめて読み込む「フォルダーから」の結合は、フォルダ内のファイルが増えるほど時間がかかります。使い続けるうちに少しずつ遅くなっていく、というのがよくある形です。
確認すること
- フォルダに何ファイルあるか（エクスプローラーで件数を確認する）
- 集計に使わない過去のファイルが残っていないか
- 対象外のファイルが混ざっていないか（別の形式のファイル、ファイルを開いている間にできる「~$」で始まる一時ファイル、手作業で作ったバックアップのコピーなど）
- サブフォルダの中まで読み込んでいないか
対処
- 結合するより前のステップで、拡張子・ファイル名・フォルダのパスでフィルターをかける
- 処理済みの古いファイルは、別のフォルダ（例：過去分）へ移す
- 過去の分も集計に必要なら、過去分を1つのファイルにまとめておき、毎月の新しいファイルだけをフォルダ結合で読み込む形を検討する
ファイル名で絞り込む場合は、条件に合わない名前のファイルが追加されたときに取り込み漏れが起きていないか、読み込んだファイルの数で確認します。
確認5：プライバシーレベルの設定
複数のデータソース（ファイル同士、ファイルとWeb、ブック内のセルとファイルなど）を組み合わせるクエリでは、データソースの間でデータが渡るときにPower Queryが確認を行います。この設定によって処理のしかたが変わり、更新が遅くなることがあります。
確認する場所
- 「データ」タブ →「データの取得」→「データソース設定」で、データソースごとのプライバシーレベルを確認する
- 「クエリのオプション」の「現在のブック」→「プライバシー」で、このブックでのプライバシーレベルの扱いを確認する
判断の考え方
- どれも社内で自分が管理しているファイルなら、各データソースのプライバシーレベルを同じもの（例：組織）にそろえると、処理のしかたが変わることがあります
- 「プライバシーレベルを無視する」設定で速くなる場合もあります。ただし外部のデータと社内のデータを組み合わせているブックでは、データが意図しない先へ送られるのを防ぐ仕組みを外すことになります
- 社内に設定のルールがあれば、それに従ってください
この確認でも、変更前後の時間を記録表に残します。速くならなかったときは、元の設定に戻しておくのが無難です。
確認6：バックグラウンド更新と自動更新の設定
バックグラウンド更新は、更新を速くするための設定ではなく、更新中もExcelを操作できるようにするための設定です。
- 複数のクエリがバックグラウンドで続けて更新されると、どのクエリがいつ終わったのか分かりにくくなり、遅い原因を特定しにくくなる
- 「ファイルを開くときにデータを更新する」や「定期的に更新する」が設定されていると、作業中に思わぬタイミングで更新が始まり、遅く感じる原因になる
- クエリの更新が終わる前に、ピボットテーブルの更新が始まっていないか
測って原因を絞り込み終えたら、バックグラウンド更新の設定は普段の使い方に合わせて戻します。
確認の順番のまとめ
| 順番 | 確認すること | 変更前後で見る数字 |
|---|---|---|
| 1 | どのクエリが遅いか | クエリごとの更新時間 |
| 2 | 読み込む行・列、読み込み先 | 更新時間、結果の行数と合計 |
| 3 | 参照やマージの重なり | 更新時間、そのクエリを参照しているクエリの数 |
| 4 | フォルダ内のファイル数、対象外のファイル | 更新時間、読み込んだファイルの数 |
| 5 | プライバシーレベル | 更新時間 |
| 6 | バックグラウンド更新・自動更新の設定 | 更新時間、更新が始まるタイミング |
どの確認でも、速くなったかどうかとあわせて、結果が変わっていないか（行数・合計・キーの値）を確かめてください。速くなっても、取り込み漏れが起きていては意味がありません。
