[Power Query] 大量のデータベースから、セルに指定した条件でデータを取り出したい

Power Query/Excel の勉強会でお付き合いのある方から以下のような質問をいただきました。

PowerQueryの機能には、MSクエリで可能だったセルの値を使ったパラメータークエリのような機能はないのでしょうか？大量のデータベースから、セルに指定した条件でデータが取り出せて便利だったのですが。

昨年の春頃に、Power BI Desktop および Excel Power Query - 取得と変換 で、「パラメーター」の機能が実装されました。このパラメーターを使えば、設定した値（文字や数値、日付など）の中から選択して、データ抽出の条件として使えることはわかっていました。

Deep Dive into Query Parameters and Power BI Templates
https://powerbi.microsoft.com/ja-jp/blog/deep-dive-into-query-parameters-and-power-bi-templates/

期待している Excel 利用のシナリオは、取り込みたい条件を Excel のセルで指定、たとえば、入力規則で設定したドロップダウン リストから選択などで行い、その指定した条件で元のテーブルからレコードを絞り込んでワークシートに展開する、というような使い方だと思います。

パラメーターを使って絞り込み条件を変更する

Power BI Desktop であれば、新機能の「パラメーター」を使い、 A, B, C, D などのリストで作ったパラメーターで、リストから選んだ条件で絞り込みを行い、データ モデルに結果を Load します。

等しい条件にパラメーターを指定する

この条件を変えたければ、Power BI Desktop の [ホーム] タブ - [外部データ] の [クエリを編集] から [パラメーターを編集] を選び、表示されるパラメーターの入力 ダイアログボックスで、条件（パラメーター）を変更できます。[変更の適用] を行えば、変更した条件で元のデータセットからデータを読み込み、データ モデルを更新します。

[クエリを編集] - [パラメーターの編集] を選択

[パラメーターの編集] で表示される パラメーターの入力 ダイアログ

パラメーター変更後に表示される警告

Power BI Desktop の場合、このようにクエリ エディター ウィンドウを再度開くことなくパラメーターの変更が可能です。この使い勝手は想定通りだと思います。

ところが、Excel Power Query - 取得と変換 では、同様の行の絞り込み条件とパラメーター設定をしても、Excel のリボンから [パラメーターの編集] に相当するコマンドを見つけることができず、同じ操作手順を得ることができませんでした。（使用更新チャンネルは 最新機能更新チャンネル バージョン 1611 ビルド 7571.2109）

もちろん、クエリ エディターを開けば、パラメーターを変更でき、その変更したクエリを適用・保存することで、結果のテーブルの更新ができますが、想定しているシナリオの手順ではないと思います。

条件を「引数」として渡す、それは関数化

では、リボンに [パラメーターの編集] コマンドがない間はどうすればいいか。

クエリに引数で「条件として使う値」を渡せば解決できます。事実、パラメーター管理 (Query Parameters) 実装の前は、引数と関数の組み合わせで対応していたんですよね。

サンプルとして、条件の設定は「入力規則」を設定したセルにしました。といっても、見出しとデータ部分が1行で構成したテーブルとします。

大量データのテーブルから、絞り込み条件を付けたクエリを作成します。この場合の条件は、テーブルの列のフィルター オプションで、まずは明示的に A や B を指定してます。
次に詳細エディターを使って、let の直前に、引数 param1 を使う宣言をします。

(param1 as text) =>
let
 ・・・・・
in

そして、この引数名 param1 を使って、先ほど明示的に指定したステップの条件の値（入力規則で指定し、条件として選択している A や B や C)を入れ替えます。
以下の例だと、ステップは「フィルターされた行 ＝」であり、絞り込み条件の列として使用している [区分A] に定数として指定されている "A" や "B" などを param1 に置換えます。

この追加・修正を行い、詳細エディターを閉じると以下のダイアログが表示されます。

クエリ ウィンドウ（昔はナビゲーター ウィンドウと呼んでいました）の [>] をクリックしてウィンドウを展開すると、データを絞り込むために作成したクエリーの種類は「関数」になっていることがわかります。（Table01 クエリの前のアイコンが [fx] になっています）

すでにこのクエリは「関数」になっています。この関数クエリに引数として条件の値を渡せばいいのです。

このクエリ ウィンドウは [閉じて読み込む] ことによって、ワークシートには何もロードされない [接続専用] のクエリがブックに保存されます。（パラメーターの入力ボックスに絞り込み条件を指定すれば、その条件で絞り込んだテーブルがワークシートにロードされます）

この関数クエリを使うクエリの書き方は色々な方法がありますが、条件指定の1行データ入力規則のテーブル(列名は[条件]、データは1行)から作るクエリ作成手順を紹介したいと思います。

(1) 条件指定の1行データテーブル内にアクティブカーソルを移動して、[取得と変換] の [テーブルから] を選んで、アクティブカーソルがあるテーブルからクエリの作成を行うクエリ エディターを起動します。
クエリ ウィンドウを展開すると、直前に作成した関数クエリと、いま編集しているクエリの2つが表示されます。（以下のサンプルでは Table01 と pTable1）

(2) [列の追加]タブの [全般] - [カスタム関数の呼び出し] をクリックします。これでカスタム関数を使った列の追加をするためのダイアログ ウィンドウが表示されます。

関数クエリのドロップダウン リストで直前に作成した関数クエリを選択し、引数の param1 には、[条件](これは、1行データテーブルの「列名」として指定した文字です）を指定して、[OK] をクリックします。

関数クエリを使った新しい列 [Table01] が追加されました。

(3) 新しく追加された Table01 の結果が欲しいので、[条件] 列を削除し、[Table01] の「Table」を展開ボタンを使って展開します。元の列名は使用しないのでプレフィックスのチェックを外し、[OK] をクリックします。

この例では列の順序が元のテーブルとは変わってしまいましたが、[区分A] の値で絞り込まれたテーブルのプレビューが表示されます。

 [閉じて読み込む] でクエリの結果をワークシートにロードします。

(4) 条件を変えて、クエリを実行して、テーブルの更新をします。
まず、条件設定のテーブルで他の値を選びます。サンプルでは "D" を選びました。

(5) 次に関数クエリの結果でワークシートにロードされたテーブル上で、右クリックメニューから [更新] をクリックすると、テーブルが指定した条件で絞り込まれたものに更新されます。

いかがでしょう。たぶん、このようなシナリオを想定しているのではないでしょうか。

まとめ

パラメーター(Query Parameters)を使って、ダイナミックに変数、条件を変え、クエリ―結果も変えることができます。
クエリを関数にして、関数の引数を動的に変更することで、パラメーターの利用と同じことが可能です。

ただ、もしメモリー上に余裕があれば、データセットに対してフィルターをかける「取得と変換」や「Power Query」の処理は、データ分析用のデータ準備として最低限にしておいていいと思います。
ある条件で絞り込んだ、生の「テーブル(表)」がワークシート上で必要、というケースは、データ件数が多くなればなるほど非現実的ですよね。（印刷のため・・・は知っています。その昔、連続帳票で何千件のレコードを印刷、、、を経験したことがありますが（笑）。）

どうしても Excel の発想から、テーブルや表に対してオートフィルターで条件を指定して絞り込みを行い、そこから何かしらの処理を行う、という手順を考えてしまいます。
もちろんその手順に問題はないのですが、フィルターをかけて計算をする、という「一括りの処理」は、分析フェーズの DAX の FILTER関数などを使い、数式内で行うのが柔軟な利用方法だと感じています。

最大100万行のワークシートをデータセットの前提としていた旧来の Excel 利用の考え方とは別に、これからは、SSAS(SQL Server Analysis Services) のインメモリ表形式モデルの「データ モデル」を Excel に実装した Power BI 系のモダン Excel では、データ モデルにできるだけ「必要なデータ」を「必要な形に変換」して「ロード」する（Power Query - 取得と変換 - ETL機能としての処理）のが基本だと感じています。そのため SQL Server 2012 以降の xVelocity の最適化の圧縮技術を惜しみなく適用しているような気がしています。

(独り言・・・でも、そのモダンな使い方をすると Power BI Desktop をすぐに使えるよう(それで十分)になるんですよね・・・）