Excel Power Query テーブルからパラメーター作成

Power Query 第17回目です。
前回はテーブルのデータを変数に代入して「ソース」の設定を行いました。今回はテーブルをパラメータークエリにして使う方法について勉強します。

くるみこ
くるみこ

今回も同じようにテーブルのデータを使う方法ですが、テーブルをパラメータークエリにする方法と作成後の使用方法について勉強します。

前回の方法とは違う方法があるということなんですね。
今回もよろしくお願いしますm(__)m

【この記事でわかることは】
・テーブルからパラメータークエリを作成する方法とその利用方法がわかります
・プライバシーレベルの変更方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・前回の内容は 自ブックのパスをテーブルにセットして活用する方法 でした。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Power Query for Excel のヘルプ
Excel の Power Query について
パラメーター クエリを作成する

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

テーブルからパラメータークエリを作成する

・前回記事で使用したサンプルデータをそのまま使用していきます。
・事前準備で [結合クエリ(自動)] のコピーを作成し名前を [結合クエリ(パラメーター使用)] に変変更しておきます。後でパラメータークエリからデータ取得できることを確認するために使用します。

テーブルからクエリを作成します

・先にテーブルを参画しておきます。
・次に [データの取得と変換] → [テーブルまたは範囲から] を選択します。
・「MyPath」と「Source」の二つのテーブルともクエリにしていきます。

・テーブルから作成したクエリの画像がこちらです。

・ [閉じて次に読み込む…] → [接続の作成のみ] → [OK] で保存します。
・この部分は、パラメータークエリ作成後の保存時に行ってもよいのですが「クエリ」の違いを確認するためにここでいったん保存しておきました。

・[閉じて次に読み込む…] で右のダイアログが表示されます。

・保存後の「クエリと接続」の画像がこちらです。「クエリ」は接続専用で作成されました。
・[mypath] と [source] 二つのクエリが作成されました。

クエリをパラメータクエリに変換する

・では、パラメータークエリに変換していきましょう。
・先ほど保存したクエリを Power Query エディター で表示します。
 (保存していない場合はすでにこの状態で開いていると思います)

・表示されている「クエリ」テーブルのデータ部分を右クリックします。

・そして表示項目から [ドリルダウン]を選択します。

・すると次の画像のように、適用したステップ [MyPath] が追加されます。

・ [MyPath] はテーブルの最初の行 {0}[MyPath] のデータとなっていることが確認できます。

・[閉じて読み込む]で 保存後の「クエリと接続」の画像がこちらです。パラメーター(TEXT)の表示になっていることが確認できます。

・ 「パラメータークエリ」は作成できたので、次からはその使い方です。

パラメータークエリ で ソースパスを設定します

・最初の事前準備で作成しておいた [結合クエリ(パラメーター使用)] を使います。

Power Query 詳細エディターを書き換えます

・パラメーターを次のように「ソース」の設定に使用します。

・変更する部分のコードは、先頭に「 // 」を入力するとコメントになります
・書き換えるのは次の部分です。変数名 → パラメータクエリ名 に変更します。

ソース = Folder.Files(mypath & source),
           ↑パラメーター↑

・[完了] 後にデータの更新状況を確認しようと思ったところ Formula.Firewall という エラー が発生しました。

Formula.Firewallの回避策を Microsoft Docs でしらべてみました。

Formula.Firewall は、異なるデータソースのデータを [クエリの結合] などで組み合わせて使用するとき発生することがあり、回避策は「該当するクエリをひとつに統合する」か、「クエリの [プライバシーレベル] を調整する」とのことでした。

・同じブック内のテーブルデータなのに「ファイアウォールエラー」なんて納得できませんけどね。

「該当するクエリをひとつに統合する」のは、ここでの目的「パラメータークエリから取得する」に反するのでパスします。

「クエリの [プライバシーレベル] を調整する」しかなさそうですね。

クエリの [プライバシーレベル] を調整します

・Power Query エディターのファイルメニューから [オプションと設定] → [クエリのオプション]

・すると次の「クエリのオプション」ダイアログが表示されます。

現在のブックだけのプライバシーレベルの場合

・次の画像のように、現在のブック「プライバシーを無視」に設定します。

グローバルなプライバシーレベルを変更する場合

グローバルでプライバシーレベルを変更すると、現在のブックだけでなくすべてのブックのプライバシーレベルが変更されます
外部データとの接続がある場合には注意が必要です

プライバシーレベル変更後に確認してみます

・次のGIF画像は、プライバシーレベル変更後に更新した時のものです。
「現在のブック」「グローバル」ともにエラーなく更新できるようになりました

ブックとソースファイルを含む「サブフォルダー」を別の場所に移してもエラー無く動作することも確認できました。

サンプルを登録しておきますので是非試してみてください

スポンサーリンク

まとめ(おわりに)

・以上で、テーブルをパラメータークエリに変換する方法とそのパラメーターを「ソースパス」に設定する方法についての勉強は終了です。

パラメータークエリと変数を使う場合を比較しておきます

項目パラメータークエリを使う場合テーブルデータを変数に代入して使う
クエリの数パラメーターの数だけ増えるクエリ数は増えない
変数の使用パラメーターを直接使用できる変数を設定して代入する必要がある
ステップの数ステップ数は増えない変数分のステップが増える
プライバシーレベル設定が必要な場合がある設定は不要

どちらも一長一短ありますね

・ パラメータークエリの気になる点は「プライバシーレベルを変更しなくてはいけない点」と「クエリの数が増える」点です。

・一方、前回記事の「変数を使う」場合の気になる点は、変数を手動で設定してテーブルのデータを代入する必要がある点です。

・どちらの方法を使うかは、使う人の好みですね。私は「手動」のほうがいいかな(^^ゞ

・でもケースバイケースだと思います。

・以上、ここまでの内容が参考になったなら幸いです(^^ゞ 

今回記事で使ったサンプルデータはダウンロードページに登録しておきます。

今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

テーブルのデータをパラメータークエリにする方法とその使用方法を勉強しました。最後に前回勉強した方法と比較してみましたがいかがでしたか? 
この続きも楽しみに待っていてね(^^)/

いや~勉強になりました!
プライバシーレベルを「常に無視する」に設定するのってなんだか怖い感じがします。少し面倒だけどブック毎に設定を変えるのがいいのかなぁ(^^;

【今回わかったことは】
・テーブルからパラメータークエリを作成する方法とその利用方法がわかりました
・プライバシーレベルの設定変更方法もわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを登録しています
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください