LWP | Power Queryライブラリを使用するための初期設定

LWP TECHNICAL ARTICLE | 160

記事要約

Power Queryライブラリを安定して利用するには、ライブラリを配置するだけでなく、Excel全体のグローバル設定と、クエリごとの更新設定をそろえる必要があります。

本記事では、プライバシーレベル、型とヘッダーの自動検出、既定の読み込み先、バックグラウンド更新、［すべて更新］の対象を順番に確認します。不要な自動処理や意図しない一括更新を防ぎ、VBAや業務用ボタンから更新順序を制御しやすい状態を作ることが目的です。

なお、画面名や利用できる項目はExcelのバージョンや接続形式によって異なる場合があります。組織でセキュリティ方針が定められている場合は、その方針を優先してください。

本記事の対象とゴール

対象読者

• Windows版デスクトップExcelでPower Queryを利用する方

• 共通のM関数やクエリを含むPower Queryライブラリを導入する方

• VBAや業務用ボタンからクエリの更新順序を制御したい方

ゴール

• Excel全体へ適用するグローバル設定を確認できる

• 各クエリの更新方法を個別に設定できる

• 手動更新、テーブル更新、業務用ボタンの使い分けを判断できる

• 設定完了後のチェック項目を一通り確認できる

第1章 この設定で何を整えるのか

本記事では、Power Queryライブラリを使用する前に、Excelで行っていただく初期設定について説明します。

Power Queryの設定には、大きく分けて次の2種類があります。

1. グローバル設定

Excel全体に適用される設定です。ライブラリを使用するExcel環境で、最初に設定します。

2. クエリごとの個別設定

各クエリの更新方法を指定する設定です。ブック内のクエリごとに確認します。

最初にグローバル設定を行い、その後、各クエリの個別設定を確認してください。

本記事の設定を行うことで、ライブラリ内のクエリや関数を正常に実行し、不要な自動処理や意図しない更新を防げるようになります。

本記事は、Windows版のデスクトップExcelを前提としています。

Excelのバージョンによって、設定項目の名称や画面の配置が一部異なる場合があります。

第2章 設定の全体像

本記事では、次の順番で設定します。

2.1 グローバル設定

3. プライバシーレベルの設定を無視する

4. 型とヘッダーの自動検出を使用しない

5. 新しいクエリを自動的にワークシートへ読み込まない

6. 新しいクエリを自動的にデータモデルへ読み込まない

2.2 クエリごとの個別設定

7. ［バックグラウンドで更新する］のチェックを外す

8. ［すべて更新でこの接続を更新する］のチェックを外す

9. その他の自動更新設定は、必要な場合だけ使用する

第3章 グローバル設定

3.1 ［クエリのオプション］を開く

Excelの［データ］タブから、次の順に選択します。

［データ］

→［データの取得］

→［クエリ オプション］

［クエリのオプション］には、次の2つの設定区分があります。

• ［グローバル］

• ［現在のブック］

本記事では、設定をExcel環境全体で統一するため、［グローバル］側を設定します。

3.2 プライバシーレベルの設定を無視する

本ライブラリを使用するための最も重要な設定です。最初に設定してください。

操作箇所

［クエリのオプション］

→［グローバル］

→［プライバシー］

設定内容

次の項目を選択してください。

［常にプライバシー レベル設定を無視します］

Excelのバージョンによっては、次のような表現で表示される場合があります。

［プライバシー レベルを無視すると、パフォーマンスが向上する場合があります］

この設定を行う理由

本ライブラリでは、Excelブック、複数のクエリ、共通関数およびデータソースを組み合わせて処理します。

プライバシーレベルによる制限で必要なデータの取得や組み合わせが停止しないように、この設定を使用します。

［現在のブック］側の設定

［クエリのオプション］には、次の設定もあります。

［現在のブック］

→［プライバシー］

本記事の標準手順では、こちらの設定は変更しません。

ブックごとに個別設定を行うのではなく、［グローバル］側で設定を統一します。

補足

プライバシーレベルは、Power Queryが複数のデータソースを組み合わせる際の情報の受け渡しを制御する機能です。詳しい仕様を確認したい場合は、Microsoftの「Power Queryのプライバシーレベル」に関する公式情報を参照してください。

3.3 型とヘッダーの自動検出を使用しない

操作箇所

［クエリのオプション］

→［グローバル］

→［データの読み込み］

設定内容

［型の検出］で、次の項目を選択してください。

［非構造化ソースの列の型とヘッダーを検出しない］

この設定を行う理由

型とヘッダーの自動検出が有効になっていると、データの内容に応じて、次のような処理ステップが自動的に追加されることがあります。

• ヘッダーの昇格

• データ型の変更

これらのステップが自動的に追加されると、Mコードが必要以上に複雑になり、処理内容の確認や保守が難しくなります。

本ライブラリでは、ヘッダーやデータ型をクエリ内で明示的に管理するため、Power Queryによる自動検出を使用しません。

3.4 新しいクエリを自動的に読み込まない

操作箇所

［クエリのオプション］

→［グローバル］

→［データの読み込み］

設定内容

［既定のクエリ読み込みの設定］で、次の項目を選択してください。

［カスタマイズした既定の読み込みの設定を指定する］

そのうえで、次の2項目のチェックを外してください。

• ［ワークシートへの読み込み］：オフ

• ［データモデルへの読み込み］：オフ

この設定を行う理由

本ライブラリには、最終結果を出力するクエリだけでなく、処理途中で使用するクエリや、他のクエリから参照される共通クエリ・共通関数があります。

これらをすべてワークシートやデータモデルへ読み込む必要はありません。

新しいクエリを作成するたびに、自動的にワークシートやデータモデルへ読み込まれることを防ぐため、既定の読み込み先をオフにします。

必要なクエリについては、作成後に読み込み先を個別に指定します。

この設定は、新しく作成するクエリの既定値です。

すでに作成されているクエリの読み込み先を変更するものではありません。

第4章 クエリごとの個別設定

グローバル設定とは別に、各クエリには更新方法に関する個別設定があります。

4.1 ［クエリ プロパティ］を開く

10. Excelの［データ］タブを開きます。

11. ［クエリと接続］を選択します。

12. 画面右側に表示された対象クエリを右クリックします。

13. ［プロパティ］を選択します。

［クエリ プロパティ］画面で、更新に関する設定を確認します。

4.2 基本となる個別設定

本ライブラリで使用するクエリは、原則として次の2項目のチェックを外してください。

• ［バックグラウンドで更新する］：オフ

• ［すべて更新でこの接続を更新する］：オフ

特に、VBAや業務用の更新ボタンを使用するブックでは、対象となるクエリについて、この2項目をオフにします。

4.3 ［バックグラウンドで更新する］

設定の意味

この項目をオンにすると、クエリの更新処理がバックグラウンドで実行されます。

バックグラウンドで更新している間も、Excel上で別の操作を続けることができます。

設定内容

チェックを外してください。

この設定を行う理由

VBAや業務用ボタンからクエリを更新する場合、次の処理をプログラム側で制御します。

• 更新の開始

• 更新完了の確認

• 複数のクエリを更新する順序

• 更新後に実行する処理

• エラーが発生した場合の処理

バックグラウンド更新を有効にすると、クエリの更新が完了する前に、VBAの次の処理へ進む場合があります。

そのため、本ライブラリでは、原則としてバックグラウンド更新を使用しません。

4.4 ［すべて更新でこの接続を更新する］

設定の意味

この項目をオンにすると、Excelの［すべて更新］を実行したときに、そのクエリも更新されます。

設定内容

チェックを外してください。

この設定を行う理由

本ライブラリでは、VBAや業務用ボタンから、必要なクエリを必要な順序で更新します。

［すべて更新］によって、処理途中のクエリや、更新順序を制御する必要があるクエリまで一括して更新されることを防ぐため、この項目をオフにします。

業務用の更新ボタンが用意されている場合は、Excelの［すべて更新］ではなく、そのボタンを使用してください。

4.5 その他の更新設定

［クエリ プロパティ］には、次のような設定もあります。

• ［定期的に更新する］

指定した時間間隔でクエリを自動的に更新します。

• ［ファイルを開くときにデータを更新する］

Excelファイルを開いたときにクエリを自動的に更新します。

• ［高速なデータ読み込みを有効にする］

データの読み込み方法を変更します。

これらは、業務上必要な場合に個別に設定します。

特別な指定がない場合は、オフまたは初期状態のままとしてください。

Excelのバージョンや接続形式によっては、一部の項目が表示されない、または変更できない場合があります。その場合は、表示されている項目だけを確認してください。

第5章 クエリの更新方法

［バックグラウンドで更新する］および［すべて更新でこの接続を更新する］のチェックを外しても、クエリを更新できなくなるわけではありません。

必要なクエリは、次のいずれかの方法で更新できます。

5.1 ［クエリと接続］から更新する

14. Excelの［データ］タブを開きます。

15. ［クエリと接続］を選択します。

16. 画面右側に表示された対象クエリを右クリックします。

17. ［更新］を選択します。

接続専用のクエリも、この方法で個別に更新できます。

5.2 出力されたテーブルから更新する

クエリの結果がワークシート上のテーブルとして出力されている場合は、次の方法でも更新できます。

18. 出力されたテーブル内のセルを右クリックします。

19. ［更新］を選択します。

この操作により、そのテーブルのもとになっているクエリが更新されます。

5.3 業務用の更新ボタンから更新する

VBAが組み込まれているブックでは、用意された［データ更新］などの業務用ボタンを使用してください。

この場合、次の内容はVBA側で制御されます。

• 更新するクエリ

• クエリの更新順序

• 更新完了の確認

• 更新後に実行する処理

• エラー発生時の処理

業務用の更新ボタンが用意されている場合は、Excelの［すべて更新］ではなく、そのボタンから更新してください。

第6章 設定完了後の確認

6.1 グローバル設定

プライバシー

［クエリのオプション］

→［グローバル］

→［プライバシー］

• ［常にプライバシー レベル設定を無視します］を選択

データの読み込み

［クエリのオプション］

→［グローバル］

→［データの読み込み］

• ［非構造化ソースの列の型とヘッダーを検出しない］を選択

• ［カスタマイズした既定の読み込みの設定を指定する］を選択

• ［ワークシートへの読み込み］をオフ

• ［データモデルへの読み込み］をオフ

現在のブック

［クエリのオプション］

→［現在のブック］

→［プライバシー］

• 変更しない

6.2 クエリごとの個別設定

［クエリと接続］

→対象クエリを右クリック

→［プロパティ］

• ［バックグラウンドで更新する］をオフ

• ［すべて更新でこの接続を更新する］をオフ

• その他の更新設定は、必要な場合のみ使用

6.3 クエリの更新方法

次のいずれかの方法で更新します。

• ［クエリと接続］で対象クエリを右クリックし、［更新］を選択する

• 出力されたテーブルを右クリックし、［更新］を選択する

• 用意された業務用の更新ボタンを使用する

まとめ

Power Queryライブラリを安定して使うには、関数やクエリを取り込むだけでなく、Excel側の実行条件を先にそろえることが重要です。

最初にグローバル設定でプライバシー、型とヘッダーの自動検出、既定の読み込み先を確認します。続いて、各クエリのプロパティでバックグラウンド更新と［すべて更新］の対象を整理します。

設定後は、業務用ボタンまたは意図した更新経路からクエリを実行し、更新順序と完了確認が設計どおりに動くことを確かめてください。

参考資料

各設定の詳細やMicrosoftの仕様を確認したい場合は、Microsoftの公式情報を参照してください。

• Power Queryのプライバシーレベル：https://learn.microsoft.com/power-query/privacy-levels

• Power Queryのデータ型：https://learn.microsoft.com/power-query/data-types

• Excelの接続プロパティ：https://support.microsoft.com/excel/connection-properties

• Excelの外部データ接続の更新：https://support.microsoft.com/excel/refresh-an-external-data-connection-in-excel

• Power Queryのクエリ管理：https://support.microsoft.com/excel/manage-queries-power-query

本記事では、Power Queryライブラリを使用するために必要な設定手順を優先して説明しています。

出典メモ

本記事は、LWP『プログラミングM言語』の「Power Queryライブラリを使用するための初期設定」をもとに、公開記事として見出し構成と導入・まとめを再構成したものです。

Microsoftの関連情報は「参考資料」に記載した各リンクを参照してください。

Copyright © LWP 山中 一弘 |