【M言語は難しくない！】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう！。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

　ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

　今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

　あくまで、Power Queryエディタ（以降エディタ）にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロを修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです！

目次

今回のポイント

今回使用するデータと行いたいこと

演習1/受注金額1位の担当者を抽出

演習２/最高値の半分未満の担当者抽出

＜まとめ＞

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、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言語を学習する効果の大きさを感じて頂けたら幸いです

長文に最後までお付き合い頂き誠にありがとうございました

次の回は、カスタム列・作成機能の裏技を使用して、特殊なテンプレートを作成します

M言語に慣れる_4回目～特殊テンプレート作成～

参考までに今回解説したファイルの完成版を添付します

にほんブログ村

【M言語は難しくない！】今回はカスタム列の隠れた裏技を使用して、特殊なテンプレートを作成します。今回は次の画像のように、「1対多」の関係の結合を「コードを1つ追加する」だけでサクッと行います

この処理は、従来であればVBAなどを覚えないとできない処理でした

M言語の仕組みを使えば、カスタム列・作成画面にて本当にサクッと作成できます

M言語の魅力のうちの一つといってもいい処理かもしれませんね！

今回作成するテンプレート

次の画像のように「担当者名のリスト」と「勤務日のリスト」から、各担当者別の勤務表を作成します

上の画像の右側/E列とF列の表のように、担当者1人に対して、複数の勤務日を結合します

勤務表は、最終的には勤務日の右に「勤務開始時刻」や「勤務終了時刻」などを追加して使用するイメージです

　解説は、シート上の「担当者名のリスト/列」と「勤務日のリスト/列」から次の２つのクエリを作成した上で、Power Queryエディタ（以降エディタ）で操作を開始するところからはじめます

・担当者名のリスト ➡ Namesクエリ

・勤務日のリスト ➡ TimeTable

次のファイルに含まれる演習用の元データは、クエリを作成する前の状態になっています

目次

今回のポイント

データ形式の事前調整

各行に日付テーブル作成

作成テーブルの展開

条件式の追加による調整

シートへの読み込み処理

＜まとめ＞

今回のポイント

今回の「カスタム列」では、前述の通り「1対多」の関係で結合を行います

次の画像のように「列」ではなく「テーブル」を結合します

この「テーブル」を結合するの点が、今回の解説の最大のポイントになります

今回作成する内容とポイントを確認したところで、本格的な解説をはじめます

データ形式の事前調整

前述の通り、解説はエディタ内に２つのクエリがある状態から開始します

・担当者名/列のリスト ➡ Namesクエリ、・勤務日/列のリスト ➡ TimeTable

過去の記事で解説した通り、「データの構造化」が行われた結果、データ形式の変更が必要な箇所が1点でてきますので、まずはそちらを変更します

下の画像の勤務日を「時刻」から「日時」に変更します

データ形式の変更は、上の画像の左上の黄色の印から行えます

上のGIF画像のように、日付を指定した後は次の画面が開きます

「現在のものを変換」をクリックすれば、下のGIF画像のように型式変更が行われます

各行に日付テーブル作成

では、「Names」クエリをエディタ画面の左から開いてください

次に「列の追加タブ」から「カスタム列」をクリックしてください

カスタム列の作成画面が下の画像のように開いたら、「新しい列名」を「勤務日」と指定してください

ところで、上の画像の右に「使用できる列」の欄があります

こちらには「担当者名」しか使用できる列がありません

挿入したいのは、勤務日であり、しかも担当者1人につき「1対多」の関係で結合する必要があります

ここからはM言語の出番です

M言語を書くといっても、実は、下の画像のようにクエリ名を指定するだけです

しかも、クエリ名を途中まで入力したところで、「入力補助」機能が働きます

クエリ名を指定したら、カスタム列作成画面の右下にあるOKボタンをクリックします

すると、記事の冒頭の「今回のポイント」で紹介した、テーブル結合が行われた画面になります

作成テーブルの展開

ここからは、中級編で解説したテーブル結合時の「展開処理」と一緒です

勤務日の右上のマークをクリックすれば、TimeTableクエリの内容が展開します

実際に上の画像のマークをクリックすると、実際には、下のGIF画像のように展開内容を指定する画面が開きます

こちらの画面では、展開項目として「勤務日」を指定するのはもちろんのこと、「元の列名をプレフィックスとして使用します」のチェックは外しておきます

こちらのチェックを外しておかないと「元のクエリの列名」も入ってしまいます

「元の列名をプレフィックスとして使用します」 のチェックを外したところで、画面下のOKボタンを押します

条件式の追加による調整

前述のテーブル展開を行った状態だと、下の画像のように、厳密には担当者名と勤務日が「1対多」の関係になっていません

上の画像の黄色に印をつけた箇所が、空欄になっていないといけません

こちらについては、黄色の箇所が空欄になる条件列を挿入します

勤務日は「2021/03/01」～「2021/03/03」の並びを繰り返しています

ですので、条件列は次の２つの内容になります

・勤務日が「2021/03/01」 ➡ 担当者名

・勤務日が「2021/03/01」以外 ➡ null（空欄）

では、上の２つの内容を、実際の条件列・作成画面に反映させます

条件列は、「列の追加」タブから「条件列」をクリックして作成画面を開きます

開いた作成画面に、前述２つの内容を設定していきます

まずは勤務日が「2021/03/01」の時の内容です

条件部分の「値」は「2021/03/01」を手入力します

あるいは、カレンダーから日付を選択することもできます

上の画像右の出力の箇所は、「列名」を選択できように、下の画像の「列の選択」を指定するのを忘れないようにしてください

次は 勤務日が「2021/03/01」以外の時の内容です

2点の内容を設定してOKボタンを押すと、エディタ内に新たな列が追加されています

シートへの読み込み処理

エクセルシートに読込む際には元の担当者名の列は削除し、追加した条件列を一番左に移動しておきましょう！

また、上の画像の「勤務日.1」の列は列名を「勤務日」にし、データ形式も「任意」/下画面左から「日付形式」/下画面右に変更しておきましょう！

• 

• 

では、実際にエクセルシートに読み込み処理を行います

＜まとめ＞

　今回は、M言語を１つ入力するだけで特殊なテンプレートを作成する方法について解説を行いました

　カスタム列の作成画面にて、クエリ名/TimeTableを指定するだけで「1対多」の関係によるデータ結合が行えました

その後は、通常のデータ結合時と同じように結合内容を展開しました

今回は、上記の仕組みを使用して「担当者名」と「勤務日」のリストから勤務表を作成しましたが、他にも活用できる場面があるはずです

何より、M言語の威力を肌で感じて頂けたら幸いです

今回の解説は以上です！！

長文に最後までお付き合い頂き、誠にありがとうございました

参考までに今回使用したファイルを添付します

次回は、M関数の参照機能について解説します

https://analytic-vba.com/power-query/m-code/begin-ref/

にほんブログ村