【Excel】Power Queryで増分更新を設定する!Excel大量データの読み込み時間を短縮する方法

Excelで大量のデータを扱う際、Power Queryでの読み込みに時間がかかることがあります。特にデータ量が日々増加する場合、毎回全データを再読み込みするのは非効率です。

Power Queryには「増分更新」という機能があります。これにより、最新のデータのみを読み込むように設定でき、処理時間を大幅に短縮できます。

この記事では、Power Queryで増分更新を設定する手順と、そのメリットについて詳しく解説します。大量データの扱いに悩んでいる方は、ぜひ参考にしてください。

Power Query増分更新の仕組み

Power Queryの増分更新は、データソースの更新頻度とデータ量に応じて、効率的にデータを読み込むための機能です。通常、Power Queryでデータを更新する際は、データソース全体を最初から最後まで読み込み直します。

しかし、データ量が膨大になると、この全量読み込みにはかなりの時間がかかります。特に毎日、あるいは毎時間データを更新する必要がある場合、この待ち時間は業務のボトルネックとなり得ます。

増分更新のメリット

増分更新を導入することで、以下のようなメリットが得られます。まず、処理時間の短縮が挙げられます。最新のデータのみを対象とするため、更新にかかる時間が劇的に短くなります。

次に、リソースの節約です。全データを読み込まないため、CPUやメモリへの負荷が軽減され、PCの動作が軽くなる可能性があります。これにより、他の作業と並行して更新を行うことも容易になります。

さらに、リアルタイムに近いデータ更新が可能になります。更新頻度を上げても処理時間が短いため、常に最新のデータにアクセスできる環境を構築しやすくなります。

増分更新を設定するための前提条件

増分更新をPower Queryで設定するには、いくつかの前提条件があります。最も重要なのは、データソースに更新を判断するための列が用意されていることです。一般的には、日付やタイムスタンプの列が使用されます。

この列は、データの追加や変更があった際に、どのデータが新しいかを識別するために不可欠です。例えば、「更新日時」や「作成日」といった列が存在しない場合、増分更新の設定はできません。

また、Power Queryエディターでデータソースが読み込まれている必要があります。まだPower Queryでデータソースを読み込んでいない場合は、まずその作業を完了させてください。

Power Queryで増分更新を設定する手順

Power Queryで増分更新を設定する手順は、以下の通りです。まず、Power Queryエディターを開き、対象のクエリを選択します。

1. 「ホーム」タブから「増分更新」を選択
   Power Queryエディターの「ホーム」タブにある「クエリ」グループ内に、「増分更新」というボタンがあります。これをクリックしてください。
2. 増分更新の設定ダイアログを開く
   「増分更新」をクリックすると、「増分更新」ダイアログが表示されます。ここで、増分更新の主要な設定を行います。
3. 増分更新の有効化
   ダイアログの上部にある「増分更新」のチェックボックスをオンにします。これにより、増分更新機能が有効になります。
4. 更新を判断する列の選択
   「更新を判断する列」のドロップダウンリストから、データソースの更新を識別するための列を選択します。通常は日付やタイムスタンプの列を選びます。
5. 更新期間の設定
   次に、更新期間を設定します。ここでは、「更新する期間」と「保持する期間」を設定します。
6. 「更新する期間」の設定
   「更新する期間」では、今回更新するデータ範囲を指定します。例えば、「1 Days」と設定すると、前回の更新以降に追加された1日分のデータが対象になります。
7. 「保持する期間」の設定
   「保持する期間」では、履歴として保持するデータ範囲を指定します。例えば、「30 Days」と設定すると、過去30日分のデータのみがクエリに残ります。これより古いデータは自動的に削除されます。
8. 「完全な更新」の設定
   「完全な更新」では、増分更新の対象とならない期間のデータをどのように扱うかを指定します。
9. 「完全な更新」の対象期間
   「完全な更新」の対象期間は、「保持する期間」よりも長い期間を設定することが一般的です。例えば、「保持する期間」が30日なら、「完全な更新」は60日や90日などに設定します。
10. 「完全な更新」の処理方法
    「完全な更新」の処理方法では、「完全な更新」の期間に該当するデータに対して、増分更新ではなく全量読み込みを実行するかどうかを選択します。
11. 設定の適用
    すべての設定が完了したら、「OK」ボタンをクリックして設定を保存します。
12. クエリの保存
    Power Queryエディターの「ホーム」タブにある「閉じて読み込む」をクリックし、クエリを保存します。

増分更新の適用と確認

増分更新の設定が完了したら、実際に更新を実行して動作を確認します。Excelの「データ」タブにある「すべて更新」をクリックしてください。

増分更新が正しく設定されていれば、前回更新時からの差分データのみが読み込まれ、更新処理が短時間で完了するはずです。もし、更新に時間がかかるようであれば、設定を見直す必要があります。

また、データが期待通りに更新されているか、データ範囲が「保持する期間」で設定した日数になっているかなどを、読み込まれたテーブルで確認してください。

増分更新ができない場合のトラブルシューティング

増分更新を設定しようとしても、「増分更新」ボタンがグレーアウトしていたり、設定ができない場合があります。このような場合は、まず前提条件が満たされているかを確認してください。

更新を判断する列が存在しない

増分更新ができない最も一般的な原因は、データソースに更新を判断するための列(日付やタイムスタンプなど)が存在しないことです。この列がないと、Power Queryはどのデータが新しいかを判断できません。

対処法:

1. データソースの確認
   元のデータソース(Excelファイル、データベース、Webサイトなど)に、更新日時や作成日を示す列があるか確認してください。
2. 列の追加
   もし該当する列がない場合は、データソース側で追加するか、Power Queryエディター内で「カスタム列」を追加して、現在の日付やタイムスタンプを記録する列を作成してください。

データ型が正しくない

更新を判断する列のデータ型が、日付やタイムスタンプとして正しく認識されていない場合も、増分更新の設定ができません。例えば、日付が文字列として扱われていると、Power Queryはそれを更新判断に利用できません。

対処法:

1. データ型の変更
   Power Queryエディターで、対象の列を選択し、「変換」タブの「データ型」から「日付」や「日付/時刻」に変更してください。

クエリが複雑すぎる

対象のクエリが非常に複雑で、多くの変換処理が適用されている場合、増分更新の自動検出がうまくいかないことがあります。特に、ソースデータから直接日付列を抽出するのではなく、計算によって日付列を生成している場合などに発生しやすいです。

対処法:

1. シンプルなクエリから試す
   まずは、データソースから直接日付列を読み込むだけのシンプルなクエリで増分更新を設定し、動作を確認してください。
2. 変換処理の順序見直し
   もしシンプルなクエリで増分更新が機能する場合、元の複雑なクエリに戻し、日付列の抽出や変換処理を、他の変換処理よりも前に行うように順序を見直してください。

増分更新と完全な更新の期間設定の考え方

増分更新を設定する際に、「更新する期間」と「保持する期間」、そして「完全な更新」の期間設定は、データの性質や利用目的に合わせて慎重に決める必要があります。

「更新する期間」と「保持する期間」

「更新する期間」は、前回の更新以降に増分更新の対象となるデータ範囲を定義します。例えば、日次更新であれば「1 Days」、週次更新であれば「1 Weeks」と設定することが一般的です。

「保持する期間」は、データがExcelファイル内にどれだけ蓄積されるかを決定します。例えば、「保持する期間」を30日とした場合、30日より古いデータは更新時に自動的に削除され、ファイルサイズが過度に増大するのを防ぎます。ただし、履歴として必要なデータは、この期間内に収まるように設定する必要があります。

「完全な更新」の期間と処理方法

「完全な更新」は、増分更新の対象とならない、より古い期間のデータをどのように扱うかを定義します。通常、「保持する期間」よりも長い期間を設定します。例えば、「保持する期間」が30日、「完全な更新」の期間を90日と設定した場合、過去90日分のデータは保持されますが、そのうち直近30日分のみが増分更新の対象となり、それより前の60日分は「完全な更新」として一度だけ読み込まれます。

「完全な更新」の処理方法では、この「完全な更新」期間のデータに対して、増分更新と同じように差分のみを読み込むのか、それとも全量読み込みを行うのかを選択できます。データソースによっては、古いデータに対しては全量読み込みしかサポートしていない場合があるため、データソースの仕様に合わせて選択してください。

Power QueryとVBAの使い分け

大量のデータを扱う際に、Power QueryとVBAのどちらを使うべきか迷うことがあります。それぞれに得意な処理と不得意な処理があります。

項目	Power Query	VBA
主な用途	データ抽出、変換、結合、読み込み	Excel操作の自動化、複雑な計算、カスタム機能開発
データ量への対応	大量データに強い(増分更新で効率化)	大量データは処理が遅くなる傾向
学習コスト	GUI操作中心で比較的容易	プログラミング知識が必要
更新処理	増分更新で効率化可能	全量再処理になりがち
複雑な条件分岐	条件列などで対応可能	柔軟に対応可能

Power Queryは、外部データソースからのデータ取得、整形、結合といったETL(Extract, Transform, Load)処理に非常に優れています。特に増分更新機能を使えば、大量のデータを効率的に管理できます。

一方、VBAはExcelの操作自体を自動化したり、ユーザー定義関数を作成したりするのに適しています。特定のセルへの値の書き込みや、条件に応じた複雑な判断処理など、Power Queryだけでは難しい細かい制御が可能です。

したがって、大量のデータを外部から取得・加工してExcelに取り込む場合はPower Queryを、取り込んだ後のExcel内でのさらなる自動化や、より柔軟な条件処理を行いたい場合はVBAを、というように使い分けるのが効果的です。

まとめ

Power Queryの増分更新機能を使えば、Excelで扱う大量データの読み込み時間を劇的に短縮できます。更新を判断する列を設定し、適切な期間を定義することで、日々のデータ更新作業が格段に効率化されます。

この記事で解説した設定手順を参考に、ぜひ増分更新を導入してみてください。これにより、データ更新の待ち時間が減り、より生産的な業務が可能になります。

大量データ管理の効率化は、Power Queryの増分更新機能の活用から始めましょう。

📊

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

🏆

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