【M言語は難しくない！】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう！。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。
例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです
ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします
今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません
あくまで、Power Queryエディタ（以降エディタ）にて、既に数式バーに記録されているM言語を修正するだけです
マクロ作成で言えば、記録マクロを修正するような内容です
記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです！
目次
今回のポイント
ステップ名変更
実は、エディタのステップ名は自分好みに修正していけます
今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります
参照ステップ
前々回、ステップをスキップして参照することで「参照ステップ」を作成しました
前々回は平均値を参照しましたが、今回は「最高値」を参照します
クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします
数式バーへの参照ステップ組み入れ
一度、数式バーにてダミー値でフィルタリングを行います
ダミーで設定をした箇所に、前述の参照ステップを組み入れます
今回使用するデータと行いたいこと
今回は次のデータを使用します
担当者別に受注金額を管理する表です
こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします
演習の中で、こちらの表にデータを新たに追加したりします
今回のポイントと使用するデータを確認したところで、本格的な演習に入ります！
演習1/受注金額1位の担当者を抽出
ステップ名を変更
まず、表をテーブル化してエディタを開きます
表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします
エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います
下の画像の「変更された型」を「DATA」に変更しましょう
クエリ名を変更するのと同じ要領でステップ名も変更できます
「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます
なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります
この点は後述します
参照ステップを作成/全体最高値の算出
次に、受注金額の列を選択して「変換タブ」の「統計」から最大値をクリックします
すると上のGIF画像のように、受注金額の最大値が算出されます
ここでも、ステップ名は変更しましょう
この時点では、適用したステップの欄は次の画像のようになっています
最後のステップの「計算された最大」を「Cal-Max」に変更しておきましょう
さて、
ここから、「Cal-Max」を参照ステップとして確定する処理をします
確定処理のために、下の画像の数式マークをクリックします
すると、「Cal-Max」のステップが１つ繰り上がります
上のGIF画像のように「Cal-Maxステップ」の後に「カスタム列１」というステップができます
追加された「カスタム列１」ステップの数式バーは下の画像のように、最大値を算出してある前ステップを参照しています
なお、数式バーが「Cal-Max」ではなく「＃”Cal-Max”」というステップ名になっています
こちらは、前述の「ステップ名の変更」でも少し触れましたが、ステップ名が空欄やｰなどの記号で文字列が繋げられている場合、#”ステップ名”になります
この点は、必ず意識しておきましょう
「最高値」の担当者を抽出
この＜受注金額が「最高値」の担当者を抽出＞では主に、３つのことを行います
①前ステップをスキップ ➡ ②最高値をフィルタリング ➡ ③シートへの読込
上記の３つを通じ、前述の参照ステップを活用してダイナミックにフィルタリングを行えるようにします
①前ステップをスキップ
参照ステップを確定した段階では、エディタ内の表示は「Cal-Max」ステップで算出した「最高値」だけが表示されています
最終ステップの「カスタム１」ステップの数式バーの内容を、１つ前の「Cal-Max」をスキップした、「DATA」ステップの参照に変更します
これで、上のGIF画像のようにエディタの表示が、参照ステップを確定する前の表示に戻りました
②最高値をフィルタリング
では、記事の冒頭でも紹介したように、まずはダミー値で受注金額をフィルタリングします
受注金額をフィルタリングするには、まずは下の画像の▼マークをクリックします
それから、下の画像の黄色の箇所にダミーとして、表示されている金額の内の「どれか１つ」を入力します
すると、数式バーが次の画像のような表示になっているはずです
但し、上の画像の右側にある、緑色で自動記録された「50000」はあくまでダミーです
こちらの「50000」を参照ステップの内容に書き換えます
前述したように、#”ステップ名”にしないとエラーになりますので注意が必要です
これで、フィルタリングがダイナミックになったので、エクセルシートに読込みます
③シートへの読込
エクセルシートに読込む前に、担当者名以外の列は削除しておきます
下のGIFが、エクセルシートに読込んだ時の内容です
下の図の左、元データが降順で並んでいるので、受注金額が最高の担当者が正しく抽出されていることがよく分かると思います
では、元データに新たに「既存の最高値」を超える担当者/TESTを加えてみます
そして、エクセルシートに読込んだクエリを更新します
きちんとダイナミックフィルタリングができていることが、上のGIFで確認できました！
演習２/最高値の半分未満の担当者抽出
こちらについては、演習1で作成したクエリを複製し、フィルタリング条件を変更するだけで完成です
クエリ複製
エディタ内で、演習1で作成したクエリの上で右クリックし、複製をクリックします
ちなみに、上の画像にあるように演習1のクエリは「Max-Member」というクエリ名で作成してあります
フィルタリング条件変更
前述のクエリの複製を行ったら、下の画像のようにクエリ名をF2キーで変更しておきましょう
クエリ名を変更したら、クエリを開き次の画像の「フィルターされた行」ステップをクリックします
こちらの数式バーにある、条件式の部分を変更します
演習2でフィルタリングに設定する条件は「最高値の半分未満」ですので、上の画像の赤丸部分を次の画像のように変更します
符号についても、変更を行うのがポイントです
シートに読み込み
フィルタリング条件を変更したところで、読込処理を行います
読込先は演習1の下にします
最高値は担当者/TESTの120,000なので、最高値の半分/60,000未満の担当者が正確に抽出されています
では、最高値を130,000に引き上げてみましょう！
そうすると、受注金額が60,000の担当者も抽出対象になるはずです
クエリを更新すると、下のGIFのように受注金額60,000の担当者があらたに抽出されています
これで、演習2で作成したクエリもダイナミックフィルタリングが行えていることを確認できました
＜まとめ＞
今回は、前々回に解説した「参照ステップ」をフィルタリングに活用してダイナミックフィルタリングを行うクエリを２つ作成しました
演習1のクエリでは、「最高値」の担当者を自動で抽出できるようにしました
演習2のクエリでは、「最高値」の半額未満の担当者を自動で抽出できるようにしました
２つのクエリともに、数式バーに記録されたM言語を編集するだけでフィルタリングをダイナミックなものにしました
今回の解説を通じて、M言語を学習する効果の大きさを感じて頂けたら幸いです
長文に最後までお付き合い頂き誠にありがとうございました
次の回は、カスタム列・作成機能の裏技を使用して、特殊なテンプレートを作成します
参考までに今回解説したファイルの完成版を添付します
コメントを残す