【Excel】Power Queryの接続を「接続のみ」に変更!Excelシートへの出力を止めてデータを保持する方法

Power Queryで取得したデータをExcelシートに出力したくない場合があります。しかし、接続設定が「テーブルまたはPower Pivotウィンドウ」になっていると、必ずシートに出力されてしまいます。この設定を変更することで、データのみを保持し、シートへの出力を停止できます。この記事では、Power Queryの接続設定を「接続のみ」に変更する方法を解説します。

Excelシートへの不要なデータ出力を防ぎ、Power Queryで取得したデータを内部で保持したい場合に役立ちます。この設定により、Excelファイルが不要なデータで肥大化するのを防ぎ、クリーンな状態を保てます。

Power Queryの「接続のみ」設定の目的と仕組み

Power Queryは、外部データソースからデータを取得・整形し、Excelシートやデータモデルに読み込むための強力なツールです。通常、取得したデータはExcelのテーブルとしてシートに表示されます。しかし、取得したデータを直接シートに表示せず、他のクエリでの加工やデータモデルでの集計にのみ利用したい場合があります。

そのような場合に「接続のみ」という設定が有効です。この設定は、クエリがデータを取得・整形するプロセスは実行しますが、その結果をExcelシート上にテーブルとして表示する操作をスキップします。データはPower Queryの内部メモリに保持され、他のクエリから参照可能になります。

Power Queryの接続設定を「接続のみ」に変更する手順

Power Queryで取得したデータをExcelシートに出力しないようにするには、クエリのプロパティを変更する必要があります。以下の手順で設定を行ってください。

1. Power Queryエディターを開く
   Excelのリボンメニューから「データ」タブを選択し、「データの取得と変換」グループにある「データの取得」をクリックします。使用したいデータソースを選択し、データを読み込んでPower Queryエディターを開いてください。既存のクエリを編集する場合は、「データ」タブの「クエリと接続」から該当のクエリをダブルクリックします。
2. クエリのプロパティを表示する
   Power Queryエディターが開いたら、左側の「クエリ」ペインで設定を変更したいクエリを右クリックします。表示されたコンテキストメニューから「プロパティ」を選択してください。
3. 「Excelへの読み込み」設定を変更する
   表示された「クエリのプロパティ」ダイアログボックスで、「接続プロパティ」タブを選択します。このタブの中に、「Excelへの読み込み」という設定項目があります。
4. 「接続のみ」を選択する
   「Excelへの読み込み」のドロップダウンリストをクリックし、表示されるオプションから「接続のみ」を選択します。
5. 設定を閉じる
   「OK」ボタンをクリックして、「クエリのプロパティ」ダイアログボックスを閉じます。
6. クエリを更新して設定を反映させる
   Power Queryエディターの「ホーム」タブにある「閉じて読み込む」ボタンの▼をクリックし、「閉じて次に読み込む」を選択します。表示される「データのインポート」ダイアログボックスで、「接続のみ」が選択されていることを確認し、「OK」をクリックします。これで、クエリはExcelシートにデータを読み込まず、接続情報のみを保持するようになります。

この設定により、クエリはバックグラウンドでデータを取得・変換しますが、Excelシート上には何も表示されません。取得したデータは、別のクエリで参照したり、Power Pivotなどのデータモデルに読み込んだりするために利用できます。

「接続のみ」設定時の注意点とよくある誤解

「接続のみ」設定は非常に便利ですが、いくつかの注意点や誤解しやすい点があります。これらの点を理解しておくことで、意図しない結果を防ぎ、Power Queryをより効果的に活用できます。

データがどこに保持されているか

「接続のみ」に設定した場合、データはExcelシート上には表示されません。では、どこに保持されているのでしょうか。データはPower Queryの内部メモリにキャッシュされます。これは、Excelファイルを開いている間、またはクエリが更新されるまで利用可能な状態です。

このキャッシュされたデータは、同じワークブック内の他のPower Queryクエリから参照することができます。例えば、あるクエリで取得・整形したデータを基に、別のクエリでさらに集計や加工を行うようなシナリオで役立ちます。ただし、Excelファイルを閉じると、このキャッシュされたデータは失われます。次回ファイルを開いた際には、クエリの更新が必要になります。

「読み込み先」の選択肢について

「クエリのプロパティ」ダイアログボックスで「Excelへの読み込み」の設定を変更する際、「テーブル」や「Power Pivotウィンドウ」といった選択肢があります。これらは、クエリの結果をExcelシートのテーブルとして表示するか、Power Pivotモデルに読み込むかを選択するものです。

「接続のみ」を選択した場合、これらの「読み込み先」の選択肢は無効になります。これは、「接続のみ」が、データをExcelシートやデータモデルに直接「読み込む」のではなく、あくまで「接続」情報だけを保持する設定だからです。もし、データをPower Pivotで集計したい場合は、「接続のみ」ではなく「Power Pivotウィンドウ」を選択する必要があります。

クエリの更新と「接続のみ」の関係

「接続のみ」に設定したクエリも、元データの変更に応じて更新が必要です。更新を実行すると、Power Queryは再度データソースに接続し、最新のデータを取得・整形します。ただし、前述の通り、この更新されたデータはExcelシートには表示されません。

更新されたデータを利用するには、そのクエリを参照している別のクエリを更新するか、またはそのクエリ自体を「テーブル」や「Power Pivotウィンドウ」に読み込む設定に変更し直す必要があります。単に「接続のみ」に設定しただけでは、データは取得されても、それを活用するための具体的なステップが必要になります。

既存のテーブルを「接続のみ」に変更する際の注意

すでにExcelシートにテーブルとして読み込まれているクエリを「接続のみ」に変更したい場合、注意が必要です。まず、そのクエリがExcelシート上にテーブルとして存在していることを確認してください。もし、そのテーブルを削除してしまうと、Power Queryの接続情報も失われてしまう可能性があります。

安全な手順としては、まずクエリのプロパティを変更して「接続のみ」に設定します。その後、Power Queryエディターから「閉じて読み込む」を選択する際に、「接続のみ」が選択されていることを確認します。この手順で設定すれば、Excelシート上のテーブルは削除されずに残りますが、以降の更新ではシートにデータは出力されなくなります。もし、シート上のテーブルが不要であれば、その後手動で削除してください。

Power Queryの接続設定と他の機能との連携

Power Queryの「接続のみ」設定は、他のExcel機能やPower BIといったツールと連携する際に、その真価を発揮します。データをExcelシートに直接表示しないことで、ファイルサイズを抑えたり、データ処理の効率を高めたりすることが可能になります。

Power Pivotとの連携

「接続のみ」に設定されたクエリは、Power Pivotデータモデルのソースとして利用できます。Power Pivotは、大量のデータを効率的に処理・分析するための機能です。Power Queryで取得・整形したデータをPower Pivotに読み込むことで、複雑な集計やリレーションシップの構築が可能になります。

例えば、複数の異なるデータソースから取得したデータをPower Queryで結合・整形し、それぞれを「接続のみ」で保持しておきます。その後、Power Pivotでこれらのクエリを参照してデータモデルを作成すれば、Excelシートを煩雑にすることなく、強力な分析基盤を構築できます。これは、データ分析担当者やBIエンジニアにとって非常に有用なワークフローです。

他のPower Queryクエリからの参照

「接続のみ」に設定されたクエリは、他のPower Queryクエリから参照(マージ、追加など)することができます。これにより、データ処理のモジュール化が容易になります。例えば、日付データや顧客マスターデータなど、複数のクエリで共通して利用するようなデータセットを、「接続のみ」のクエリとして作成しておくと便利です。

別のクエリでこれらの「接続のみ」クエリを参照する際には、Power Queryエディターの「ホーム」タブにある「新しいソース」>「他のソース」>「Excelブックからの接続」などを選択し、現在開いているExcelファイル内のクエリを参照する形になります。これにより、データの一貫性を保ちつつ、効率的なデータ変換パイプラインを構築できます。

Power BIとの連携

Power BIは、Microsoftが提供するビジネス分析サービスです。Power BI DesktopでPower Queryを使用してデータを取得・整形し、Power BIサービスに発行する際にも、「接続のみ」設定は有効です。Power BIでは、データセットとしてデータが管理されるため、Excelシートへの直接的な読み込みは必須ではありません。

Power Queryで取得したデータをPower BIのデータモデルに読み込む場合、Excelファイル内では「接続のみ」に設定しておくことで、Excelファイル自体の管理をシンプルに保つことができます。Power BI側でデータ更新やレポート作成を行うため、Excelファイルはデータソースへの接続情報と整形ロジックを保持する役割に限定されます。

まとめ

Power Queryの接続設定を「接続のみ」に変更することで、Excelシートへの不要なデータ出力を停止し、データを内部で保持できます。この設定は、クエリのプロパティから簡単に行うことが可能です。

「接続のみ」設定を活用することで、Excelファイルサイズを削減し、Power Pivotや他のクエリ、Power BIとの連携をスムーズに行えます。ぜひ、この設定を理解し、データ処理の効率化に役立ててください。

📊

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

🏆

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