【Excel】ExcelでPower Queryの参照クエリだけ更新対象から外れる時のエラー解消法

Power Queryで作成したクエリのうち、参照クエリだけが更新されずに困っていませんか?参照クエリは他のクエリをベースとしてデータを加工するため、その依存関係が更新処理に影響を与えることがあります。本記事では、参照クエリが更新対象から外れる原因を整理し、具体的な確認手順を解説します。また、社内環境で注意すべき設定や管理者への確認ポイントもあわせて紹介します。

1. 参照クエリが更新されない主な原因

1-1. ベースクエリが更新されていない

参照クエリは必ずベースとなるクエリを参照しています。ベースクエリが更新されていないと、その結果を利用する参照クエリも更新されません。特に、Excelブックを開いたときに自動更新が行われない設定になっている場合や、手動更新を実行した際にベースクエリだけ更新対象から外れているケースがあります。

1-2. 更新順序の設定が不適切

Power Queryでは、複数のクエリを更新する順序を「更新の管理」で指定できます。参照クエリがベースクエリよりも先に更新されるように設定されていると、参照クエリは古いデータで更新されるか、更新自体がスキップされる可能性があります。デフォルトでは更新順序は未設定(自動)ですが、依存関係が複雑な場合は明示的に順序を指定する必要があります。

1-3. プライバシーレベルの制限

Power Queryには、データソースのプライバシーレベル(プライベート、組織、パブリック)を設定する機能があります。参照クエリが異なるプライバシーレベルのデータソースを結合している場合、結合を防止するためにクエリの更新がブロックされることがあります。これにより、参照クエリだけが更新されない状態になります。

1-4. バックグラウンド更新が無効

Excelのクエリプロパティには「バックグラウンド更新」の設定があります。このオプションが無効になっていると、クエリの更新が他の処理をブロックする可能性があり、特に複数のクエリを連続して更新する際に参照クエリの更新がスキップされることがあります。

1-5. クエリのエラーや読み込み設定の不一致

参照クエリのM式にエラーがある場合、更新が途中で止まります。また、「読み込み」の設定が「接続のみ」になっていると、Excelシートへの読み込みは行われませんが、他のクエリから参照される場合は更新されます。ただし、「接続のみ」でありながら参照クエリとして使用されていない場合など、設定の不一致が原因で更新対象から外れることがあります。

2. 原因を切り分ける確認手順

以下の手順で、原因を特定してください。

1. クエリの依存関係を確認する
   Power Queryエディターを開き、左側の「クエリ」ペインで参照クエリを右クリックして「依存ビューに移動」を選択します。ベースとなっているクエリが表示されるので、そのクエリが最新の状態かを確認してください。
2. ベースクエリの更新状態を確認する
   「データ」タブの「クエリと接続」を開き、ベースクエリの最終更新日時を確認します。もし過去の日時であれば、ベースクエリが更新されていない可能性があります。その場合はベースクエリを手動で更新し、その後参照クエリが更新されるか試してください。
3. 更新順序を設定する
   「データ」タブ→「クエリと接続」→「更新の管理」を開きます。表示されたクエリ一覧で、ベースクエリを参照クエリより先に配置してください。順序はドラッグ&ドロップで変更できます。設定後、「OK」をクリックし、再度すべてのクエリを更新してみてください。
4. プライバシーレベルを確認する
   「ファイル」→「オプション」→「現在のブック」→「プライバシー」で、「高速データ結合のためにプライバシーレベルを無視する」にチェックが入っているか確認します。業務上問題がない場合は、このチェックを入れることでプライバシーレベルの制限を解除できます。ただし、社内ポリシーで禁止されている可能性もあるため、事前に管理者に確認してください。
5. バックグラウンド更新の設定を変更する
   「データ」タブ→「クエリと接続」→該当クエリのプロパティを開き、「バックグラウンド更新を有効にする」にチェックが入っていることを確認します。無効の場合は有効にしてください。また、「更新するたびにこのデータを最新の状態に保つ」の設定もあわせて確認します。
6. 参照クエリを手動で強制更新する
   Power Queryエディター上で参照クエリを開き、「閉じて読み込む」をクリックします。これにより、依存関係にかかわらず強制的に再計算されます。この操作で正しく更新される場合、更新順序やバックグラウンド更新の設定に問題がある可能性が高いです。

3. 失敗パターンとその対処法

失敗パターン	原因	対処法
ベースクエリは更新されるが参照クエリだけ古いデータのまま	更新順序が適切でなく、参照クエリがベースクエリより先に更新されている、またはバックグラウンド更新のタイムラグ	「更新の管理」で順序をベース→参照に変更。バックグラウンド更新を有効にし、手動更新を試す。
参照クエリの更新ボタンがグレーアウトしている	参照クエリが「接続のみ」に設定され、かつExcelシートに読み込まれていない	クエリのプロパティで「読み込み」を「テーブル」または「ワークシート」に変更。ただし他のクエリから参照する目的なら「接続のみ」でも更新は可能。
更新中にエラーが発生し、参照クエリが更新されない	参照クエリのM式にエラーがある、またはデータソースにアクセスできない	Power Queryエディターで参照クエリを開き、「エラーの表示」を確認。M式を修正するか、データソースの接続設定を見直す。

3-1. 複数の参照クエリがある場合の注意点

複数の参照クエリが同じベースクエリを参照している場合、ベースクエリが1回更新されるだけで全ての参照クエリに新しいデータが反映されるはずです。しかし、各参照クエリに個別に更新順序が設定されていると、一部だけ更新されないことがあります。そのようなときは、「更新の管理」で全ての参照クエリがベースクエリの後に配置されていることを確認してください。

4. 管理者に確認すべき設定項目

4-1. プライバシーレベルのポリシー

組織のセキュリティポリシーにより、Power Queryのプライバシーレベルを無視する設定が許可されていないことがあります。管理者に「高速データ結合のためにプライバシーレベルを無視する」設定を有効にしても問題ないか確認してください。また、データソースごとにプライバシーレベルを個別に設定する必要がある場合、管理者に適切なレベルを問い合わせてください。

4-2. データソースの資格情報

参照クエリがデータベースや共有フォルダーなどの外部データソースにアクセスしている場合、その資格情報が適切に保存されているか確認する必要があります。資格情報の保存方法(Windows認証、Basic認証、OAuthなど)は管理者が把握しているはずです。更新時に資格情報を要求される場合は、管理者に適切な認証方法を確認してください。

4-3. グループポリシーによるPower Queryの制限

企業環境ではグループポリシーにより、Power Queryのアドインが無効化されている、または特定の機能(例:バックグラウンド更新)が制限されていることがあります。管理者にExcelのアドイン設定やPower Queryのバージョンを確認してもらい、必要な機能が有効になっているか調べてください。

5. よくある質問(FAQ)

Q1. 参照クエリだけ手動更新することはできますか?

できます。「データ」タブの「クエリと接続」で参照クエリを右クリックし、「更新」を選択すれば、単独で更新できます。ただし、ベースクエリが古い場合は参照クエリも古いデータで更新されます。

Q2. ベースクエリを更新すれば、参照クエリも自動更新されますか?

自動更新される場合とされない場合があります。更新順序が適切に設定されていれば、ベースクエリの更新後に参照クエリも自動で更新されます。しかし、バックグラウンド更新が無効だったり、順序が逆になっていると自動更新されません。そのため、更新順序の設定が重要です。

Q3. 会社のポリシーでプライバシーレベルを変更できません。どうすればいいですか?

管理者に相談し、データソースのプライバシーレベルを全員で統一するか、参照クエリが結合するデータソースを同じプライバシーレベルに変更してもらってください。あるいは、参照クエリのM式を変更して、結合を避ける方法もあります。

Q4. 更新時に「この操作は許可されていません」と表示されます。

これは多くの場合、プライバシーレベルの制限によるものです。上記の対処法に従ってプライバシーレベルの設定を見直すか、管理者に確認してください。また、データソースの接続設定で「資格情報」が正しく設定されているかも確認してください。

Q5. 参照クエリを大量に使っていますが、更新に時間がかかります。改善方法はありますか?

更新順序を最適化し、バックグラウンド更新を有効にすることで、処理が並列化され時間短縮につながる可能性があります。また、クエリのステップを簡素化する、不要な列を削除するなど、クエリ自体のパフォーマンスチューニングも検討してください。

6. まとめ

参照クエリだけ更新対象から外れる問題は、依存関係の確認と更新順序の設定を見直すことで大半が解決します。まずはベースクエリが正しく更新されているかを確認し、次に「更新の管理」で順序を適切に設定してください。プライバシーレベルやバックグラウンド更新の設定も重要なチェックポイントです。社内環境では管理者の権限が必要となる設定もあるため、必要に応じて相談しながら対応を進めてください。本記事の手順を実践することで、Power Queryの更新に関するトラブルを効率的に解決できるはずです。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】