Power BI-クエリエディタでパラメーター設定するときに出てくる「クエリ」について

前回の記事（パラメーターの設定）で、Power BI desktopのクエリエディタでデータソースを変数（パラメーター）にして、データソースとして指定したExcelファイルのファイルパスをパラメーター化しました。

パラメーターを新規作成する画面で、「提案された値」に選択肢が次の3つありました。

1. 任意の値
2. 値の一覧
3. クエリ

1と2は選択すればどういう仕組みかは直感的にわかるんだけど、3「クエリ」については、そもそも何も設定していないと、その後の「クエリ」の選択ボックスで何も選択できないため、直感的に理解を進めることができない。

なので、基本的な使い方をまとめておく。

この「クエリ」を設定することで、クエリエディタを閉じた後の「データ画面」でクエリエディタで操作しなくてもクエリ（つまり、「データ画面」で操作する対象のデータセット）を変更することができる。

1. サンプルデータを取り込む

ひとまず説明用に、いつものようにMicrosoftのサンプルページから「Financial Sample.xlsx」を使わせてもらいます。

適当にダウンロードファイルをどこかに保存して、Power BI desktopを立ち上げます。

データソースはExcel fileなので、エクセルマークを選択して、

取り込むファイルを選択して、「開く」をクリック。
今回は「クエリエディタ」画面での操作がメインなので、「データの変換」をクリックして、「クエリエディタ」へ移動します。

2. 「取り込んだデータの単位」＜「想定しているレポートの単位」である場合に有用なパラメータ設定の方法（「クエリ」の利用）

取り込んだサンプルデータ「Financial Sample.xlsx」は、セグメント別、国別、Product別の売上高、原価、粗利の明細データになっています。

想定として、今回作成するレポートは各カントリーヘッド向けのレポートで、それぞれ他国の売上の状況は見れないようにしたいとします。

楽ちんな作業手順として、
①まず特定の国だけにおける明細データでレポートを作成し、
②その後はデータソースを切り替えて各国向けのレポートを複製する
という流れが想像できます。（そうするために、パラメータ設定の「クエリ」が必要になります）

3. 取り込んだサンプルデータから「国」のリストを作成する

サンプルデータには、「国」の情報が2列目に含まれています。

「国」を切り替えるための「リスト」を作りたいのですが、１からテーブルを作成するのは面倒です。
なので、まずはこのテーブルを「複製」します。

すると、おなじテーブルがもう一つできます。

この「複製」したクエリから「国（Country）」の「リスト」を作成しましょう。

「Country」列を選択して、「変換」タブ＞「リストに変換」をクリックします。

こんなリストができました。

でも、このままでは使えません。この「リスト」はファクトテーブル（今回のサンプルデータ）の列を抜粋しただけなので、同じ「国」が複数存在します。なので、リストツール「変換」＞「重複の削除」をクリックして重複を削除します。

分かりやすいように、最後に上記の作業で作成した「リスト」の名前を変えておきましょう。ここでは、一旦「L_Country」にしておきます。

4. 「リスト」があればパラメーターの「クエリ」に取り込める

ここまでの作業は「リスト」を作る作業でしたが、この「リスト」がパラメータ設定における「クエリ」を設定する際に重要な要素になります。

クエリエディタ＞「ホーム」タブ＞「パラメーターの管理」＞「新しいパラメーター」をクリックして、パラメーターを設定していきます。

パラメーターの名前をここでは、「P_Country」として、
種類は「テキスト」、提案された値は「クエリ」を選択します。
すると、先ほど作成した「リスト」である「L_Country」がクエリの選択ボックスで選択可能になっていることがわかると思います。

そしたら、初期値として「現在の値」に「Canada」ととりあえず入れておいて、「OK」をクリックして、パラメーターの設定を完了させます。

クエリエディタには、こんな感じで今設定したパラメーターが表示されます。

5. (サンプルデータの)「詳細エディタ」で作成したパラメータを設定する

いよいよ、「国（Country）」ごとにパラメーターを使ってデータをソートしていきます。

まず、ゼロから記述しなくてもいいように、クエリ「financials」上で、適当な国を1つ選らんでソートします。
※「詳細エディタ」はExcelのマクロ記録みたいな機能があるので、手動での操作がM言語で自動で記述される仕組みをうまく使います。

ここでは一旦「France」でソートしています。

そしたら、クエリエディタの「ホーム」タブ＞「詳細エディタ」をクリックして詳細エディタの画面を開きます。

「詳細エディタ」を見ると、先ほど行ったフィルター操作がM言語で記述されているのがわかります。

ここまできたら、あとは"France"という固定値を、先ほど作成したP_Countryというパラメーターに変更するだけです。

//変更前
フィルターされた行 = Table.SelectRows(変更された型, each ([Country] = "France"))

//変更後
フィルターされた行 = Table.SelectRows(変更された型, each ([Country] = P_Country))

変更したら「完了」を押して「詳細エディタ」を閉じます。

そして、クエリエディタも「ホーム」タブ＞「閉じて適用」をクリックして閉じます。

6. 「データ」画面や「レポート」画面の「データの変換」＞「パラメーターの編集」からクエリを操作して、データソースを変更できるように

先ほど、クエリエディタ上のサンプルデータ（financials)で、直接「France」でソートをかけました。では「データ」画面で「financials」テーブルを見てみましょう。

Country列は「Canada」となっています。これは、「詳細エディタ」でフィルタをP_Countryに変更したことによって、
P_Countryの初期値としておいていた「Canada」が「詳細エディタ」で読み込まれ、この「データ」画面に引き渡されたということです。

このような表示になっているということで、設定した「国」のパラメーターはうまく機能していることがわかります。

そして、ついに、、、パラメーター設定で「クエリ」を選択した際の有難みがわかります。
パラメーター化しなかったら、この国を「Canada」から「Mexco」とか他の国に変えたいときに、いちいちクエリエディタに戻ってフィルタ操作をやり直しますよね？

でもそれが不要になっています。
「データ」画面の「ホーム」タブ＞「データの変換」＞「パラメータの編集」をクリック、

すると、こんな画面が出てきて、先ほど作成した「リスト」に記載の「国」が選べるようになっています。

「国」を変更して、OKをクリックすると、「変更の適用」ボタンが出てきますので、これをクリック

僕は「Mexico」を選択したので、こんな感じで、「国（Country）」が「Mexico」に切り替わりました

この方法だと、複数のpbixブックを作らなくてもいいし、クエリエディタで何度も国（Country）別にソートをかけなおさなくてもいいので、ユーザー側にとっても利便性が向上しますね。

以上になります。