【Excel】Power Queryの更新でデータが消える!Excelの「読み込み先」設定ミスと修正手順

Power Queryで取得したデータをExcelシートに読み込んだ際、更新ボタンを押すとデータが消えてしまうことがあります。この問題は、Power Queryの「読み込み先」設定を誤っている場合に発生します。更新のたびにデータが消えてしまうと、作業効率が著しく低下します。この記事では、Power Queryでデータが消える原因と、その解決策となる「読み込み先」設定の修正手順を解説します。

Power Queryは、外部データソースからデータを取得・加工し、Excelシートに読み込むための強力なツールです。しかし、初期設定を誤ると、意図しない動作を引き起こすことがあります。特に、「読み込み先」の設定は、データの表示方法や更新時の動作に大きく影響します。この設定を正しく理解し、適切に修正することで、Power Queryの恩恵を最大限に受けられるようになります。

Power Queryの更新でデータが消える原因

Power Queryで取得したデータをExcelシートに読み込んだ際に、更新ボタンを押すとデータが消えてしまう現象は、主に「読み込み先」の設定に起因します。具体的には、Power Queryエディターで「閉じて読み込む」際に、読み込み先として「テーブル」ではなく「接続のみ」を選択した場合に発生しやすいです。この場合、データはExcelシート上には表示されず、Power Queryの「クエリと接続」ウィンドウに接続情報のみが保存されます。そのため、更新を実行しても、Excelシート上に表示されるべきデータが存在しないため、結果としてデータが消えたように見えます。

また、「テーブル」として読み込んだ場合でも、読み込み設定で「既存のワークシート」を指定する際に、誤ったセル範囲を指定している、あるいはシート自体が存在しないといったケースも考えられます。Excelは、指定された場所にデータを読み込もうとしますが、その場所が存在しない、あるいは他のデータで上書きされている場合、更新時にエラーとなったり、データが消えたりする原因となります。これらの設定ミスは、Power Queryの基本的な動作原理を理解していない場合に起こりやすいです。

Power Queryの「読み込み先」設定を修正する手順

Power Queryの更新でデータが消える問題を解決するには、「読み込み先」の設定を正しく行う必要があります。すでに「接続のみ」になっているクエリをテーブルとして読み込む場合と、テーブルとして読み込まれているが更新時に問題が発生する場合とで、手順が異なります。ここでは、それぞれのケースに対応した修正手順を解説します。

ケース1:「接続のみ」になっているクエリをテーブルとして読み込む

このケースでは、クエリはデータソースへの接続情報のみを持っています。これをExcelシート上に表示させるために、改めて読み込み設定を行います。以下の手順で、クエリをテーブルとして読み込み直します。

1. 「クエリと接続」ウィンドウを開く
   Excelのリボンメニューから「データ」タブをクリックします。次に、「クエリと接続」ボタンをクリックして、ウィンドウを開きます。
2. 対象のクエリを右クリックする
   「クエリと接続」ウィンドウに表示されている、データが消えてしまうクエリを右クリックします。
3. 「読み込み先」を選択する
   表示されたコンテキストメニューから「読み込み先」を選択します。
4. 「データのインポート」ダイアログボックスで設定する
   「データのインポート」ダイアログボックスが表示されます。「データを表示する方法」で「テーブル」を選択します。「データの格納場所」では、必要に応じて「新規ワークシート」または「既存のワークシート」を選択します。「既存のワークシート」を選択した場合は、読み込みたいシートのセルを指定します。
5. 「OK」をクリックする
   設定が完了したら、「OK」ボタンをクリックします。これで、クエリがExcelシート上にテーブルとして読み込まれます。

ケース2:テーブルとして読み込まれているが、更新時にデータが消える

このケースでは、クエリはすでにテーブルとして読み込まれていますが、更新時に問題が発生しています。これは、読み込み設定で指定したセル範囲やシートが、更新時に何らかの理由で無効になっている可能性があります。以下の手順で、クエリの読み込み設定を再確認・修正します。

1. Power Queryエディターを開く
   Excelのリボンメニューから「データ」タブをクリックします。次に、「データの取得」グループにある「クエリの編集」をクリックするか、「クエリと接続」ウィンドウで対象のクエリをダブルクリックして、Power Queryエディターを開きます。
2. 「閉じて読み込む」のドロップダウンメニューを開く
   Power Queryエディターの「ホーム」タブにある「閉じて読み込む」ボタンの右側にあるドロップダウン矢印をクリックします。
3. 「接続のプロパティ」を選択する
   表示されたメニューから「接続のプロパティ」を選択します。
4. 「使用方法」タブで設定を確認・修正する
   「接続のプロパティ」ダイアログボックスが表示されたら、「使用方法」タブを選択します。ここで、「テーブル」として読み込まれているか、あるいは「接続のみ」になっているかを確認します。「テーブル」として読み込まれている場合、読み込み先のシート名とセル範囲が正しく指定されているかを確認します。もし、指定したシートやセル範囲が存在しない、あるいは変更されている場合は、ここで正しい場所を指定し直します。「新規ワークシート」に読み込む設定になっているか、「既存のワークシート」で正しいセルが指定されているかを確認してください。
5. 「OK」をクリックして閉じる
   設定を修正したら、「OK」ボタンをクリックしてダイアログボックスを閉じます。
6. Excelシートにデータを読み込む
   Power Queryエディターに戻り、「閉じて読み込む」ボタンをクリックして、修正した設定でデータをExcelシートに読み込みます。
7. データを更新して確認する
   Excelシート上でデータを更新します。「データ」タブの「すべて更新」ボタンをクリックして、データが正しく表示されるか確認してください。

Power Queryの読み込み設定に関する注意点とよくある誤解

Power Queryの「読み込み先」設定は、データの表示方法だけでなく、更新時の動作にも大きく影響するため、正しく理解しておくことが重要です。ここでは、よくある注意点や誤解されやすいポイントについて解説します。

「接続のみ」のクエリはシートに表示されない

Power Queryエディターで「閉じて読み込む」を選択する際に、「接続のみ」を選択した場合、データはExcelシート上には一切表示されません。これは、Power Queryがデータを加工するための中間的なステップとしてのみ機能し、最終的な出力先をExcelシート以外(例:データモデル)に指定している、あるいはまだ指定していない状態であることを意味します。Excelシートにデータを表示させたい場合は、必ず「テーブル」として読み込む設定を選択する必要があります。

既存のワークシートへの読み込み時の注意点

「既存のワークシート」にデータを読み込む場合、指定したセルがデータの開始位置となります。Power Queryは、そのセルから下方向および右方向にデータを展開します。もし、指定したセルから下や右に既存のデータが存在する場合、Power Queryはそれを上書きしようとします。この上書きによって、本来残しておきたいデータが消えてしまう可能性があります。そのため、既存のワークシートに読み込む際は、十分な空きスペースがあるセルを指定することが重要です。

また、Excelのテーブル機能とPower Queryのテーブル機能を混同しないように注意が必要です。Power Queryで読み込んだデータは、Excelのテーブルとして表示されますが、これはPower Queryによって作成されたものです。Excelのテーブル機能で作成したテーブルとは、管理方法や更新の仕組みが異なります。Power Queryで読み込んだテーブルをExcelのテーブル機能でさらに加工したい場合は、Power Queryエディターで加工を終えてから、Excelシートに読み込んだ後に行うのが一般的です。

更新時のエラーと「接続のプロパティ」の重要性

Power Queryの更新時にエラーが発生する場合、その原因はデータソースへの接続情報が古くなっている、ファイルパスが変更された、あるいはシート名やセル範囲が変更された、といった「接続のプロパティ」に関わる問題であることが多いです。特に、ファイルパスが変更された場合、Power Queryは元の場所からデータを取得できず、更新に失敗します。「接続のプロパティ」の「使用方法」タブで、読み込み先のシート名やセル範囲を再確認し、必要であれば修正することで、更新エラーを解消できる場合があります。常に最新の状態に保つためには、これらの設定を定期的に確認することが推奨されます。

Power QueryとExcelテーブルの機能比較

Power QueryでデータをExcelシートに読み込むと、多くの場合、Excelのテーブル形式で表示されます。しかし、Power QueryのテーブルとExcel標準のテーブル機能には、いくつかの違いがあります。それぞれの特徴を理解し、目的に応じて使い分けることが重要です。

項目	Power Queryで読み込んだテーブル	Excel標準のテーブル
データの取得・加工	外部データソースからの取得、複雑なデータ加工が可能	Excelファイル内でのデータ整理・集計が主
更新機能	データソースの変更を自動または手動で反映できる	手動でのデータ入力・編集が基本
参照方法	クエリ名やテーブル名で参照し、数式で利用可能	テーブル名、列名で参照し、構造化参照が可能
読み込み先	新規ワークシート、既存ワークシート、データモデルなど多様	Excelシート内のみ
管理	Power Queryエディターで管理	Excelの表形式機能で管理
主な用途	データの前処理、ETL処理、定期的なデータ更新	表形式データの整理、分析、グラフ作成

Power Queryで取得したデータをExcelシートに表示させる場合、Power QueryはExcelのテーブル形式でデータを配置します。このExcelテーブルは、Power Queryのクエリと連動しており、更新操作によってデータソースの変更が反映されます。一方、Excel標準のテーブルは、手動でデータを入力・編集していく際に使用する機能です。Power Queryで取得したデータをExcelシート上でさらに手動で編集したい場合は、Power Queryの読み込み設定で「既存のワークシート」を選択し、Excelテーブルとして読み込むことで、両方の機能を活用できます。

Power Queryの「読み込み先」設定を正しく行うことで、データの更新時にデータが消えるといった問題を回避できます。特に、「接続のみ」になっているクエリをテーブルとして読み込み直す手順、および既存のテーブルの読み込み設定を確認・修正する手順を理解しておくことが重要です。これにより、Power Queryの強力なデータ取得・加工機能を、Excelシート上で安定して活用できるようになります。

今回解説した「読み込み先」設定の修正方法を試すことで、Power Queryの更新時にデータが消える問題を解決できます。今後は、クエリを作成する際に、初期設定で「テーブル」として読み込むか、あるいは「接続のみ」とするかを明確に意識してください。もし、さらに複雑なデータ処理を行いたい場合は、Power Pivotと連携してデータモデルに読み込むことも検討すると良いでしょう。

📊

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

🏆

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