こんな人むけの記事です
- Power Query（Excel含む）で、複数の条件に一致するデータを効率よく絞り込みたい方
- フィルター条件が頻繁に変わるため、Power Query内で直接編集するのではなく、別テーブルなどで条件を管理したい方
補足：日付条件などでフィルタをかけたい場合は？
以前、日付条件に関する記事も書いています。よろしければこちらも参考にしてください。
サンプルのテーブル
サンプルデータとして、このような２つのテーブルを用意しました。
複数の事業部が存在する中で、特定の部署コードに該当する従業員だけをフィルターで抽出してみます。
足したり引いたりしない項目はテキスト型に
Power BI にテーブルを取り込みました。
今回の部署コードはたまたま数字４桁になっています。本題からは少しそれますが、このようにコードが数字の場合でも、その値を足したり引いたりする必要がないものは、「テキスト型」にしておきましょう。
そうしないと、後々コードに「A1111」のような英文字が含まれるようなものが登場して、データ型の不一致エラーが発生し、更新に失敗する可能性があるからです。
ふつうはこれ使いますね？
Power Queryでフィルターをかける場合、普通はこんな感じで対象を選択、もしくは不要なものを取り外せばOKです。
今回のテーマは、この絞り込み条件を別テーブルで変更したいという要求です。
行のフィルタをかけた場合の式はこのようになります。
フィルタをかける対象テーブル
対象もPower Query に取り込みました。
取り込んだら「変換」＞「リストに変換」をクリックします。
カタチが変わってリストになりました。
フィルタ部分のMクエリを修正する
先ほどのフィルター部分の式を以下のように修正します。すると、対象テーブルにある値だけに部署コードがフィルタリングされました！ List.Containsを使っているのが特徴です。
= Table.SelectRows(変更された型, each (List.Contains(対象テーブル,[部署コード])))
詳細エディタを開いてMクエリを確認すると、このようになっています。
これで、「対象テーブル」側にて必要な部署コードを書き換えれば、更新のたびに必要な部署コードに絞った値を取得できます。
応用編：パラメータを使った絞り込み
ここまでは、Excelなどで用意したテーブルを使いましたが、別テーブルを用意するのも面倒。そのテーブルがどこに行ったか忘れちゃいそう、という場合にはパラメータを使うのもおすすめです。
ここからの内容はPower BI サービスに発行して更新をかけるので、テーブルはOneDriveやSharePointに置いた状態で進めてください。ローカルPC上のExcelファイルを参照する場合にはオンプレミスデータゲートウェイが必要です。
パラメータは、ワークスペースに発行したあとでも直接ブラウザでセマンティックモデルの設定から変更することができます。
クエリの何もないところで右クリックし、「新しいパラメーター」を選択します。
パラメータには名前を付けられるので、適当な名称と、種類をテキスト、提案された値を任意の値、現在の値にはコードをカンマ区切りで入力します。「説明」部分にはどのように入力すればよいかを書いておくと親切でしょう。
先ほど作成したパラメーターがクエリ部分に表示されました。このままだと使えないので右クリックして「参照」します。
値はただのテキストなので、「変換」＞「テキストの分割」をクリックし、区切り文字となる値にはカンマを入力してOKをクリックします。
先ほどと同じように、値がテーブル化されました。使いやすいようにクエリの名前を適当なものに変更しておきました。
あとは、従業員テーブルに戻って、フィルタの対象とするリスト名を変更してやればOKです！
パラメータの威力は発行後に発揮される
Power Queryを適用して閉じましょう。レポート画面にはテーブルの内容を表示しておき、この状態で発行しましょう。
マイワークスペースに発行してブラウザで表示させました。この段階ではPower BIデスクトップで指定した1111と2222が表示されています。
マイワークスペースを開き、対象のセマンティックモデルの三点リーダーから「設定」を選択します。
「パラメーター」という項目を開くと、先ほどPower BIデスクトップで作成した名前のパラメータと説明が表示されています。中身を変更して適用をクリックします。
2222,3333 に変えてから、セマンティックモデルの更新をかけてみます。
ばっちり、パラメータに設定したコードの対象に絞られました。
List.Contains について確認
含まれるっていうことは、もしかして部分一致になったりしない？ と少し不安になったので、パラメータに 111,333 と入力して更新をかけてみました。
結果は何も表示されなかったので、いちおう完全一致でフィルタリングできるようで安心しました。
まとめ
テーブルで指定した大量の抽出条件でフィルタをかける場合には、いったんリスト化するとその範囲にフィルタリングすることができました。
対象をリスト化するにはパラメータも使えます。こちらはテーブルを用意するのが面倒だったり、めったに値は変わらないけれどメンテナンス性を持たせたいようなときに便利です。
どんな人が書いてる？
この記事はDDさんが書いてます。お仕事でPower BIやPower Automateを触ってます。自分の備忘録を兼ねているところがありますが、ブログやQiitaでPower Platformに関する記事を書いてますので、よかったら参考にしてもらえると嬉しいです。
大阪で開かれるPower Platform系のイベントには顔を出しますので、アイコンをどこかで見かけたら声をかけてください。
おそまつさまでした。