【M言語は難しくない！】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう！！

　パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

　Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で＜赤印の「販売金額」列の1行目の「54000｣＞の箇所を抽出してみます

エクセルシート上で言えば、「=セル名（例：A９など）」を数式バーに入れる処理です

ちなみに適用したステップには、まだ１つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください！

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

　但し、「コード内の角括弧：[]と波括弧：{}の使い方のルールが分からないと小難しく感じてしまう」ただそれだけなのです

逆に、[]と{}の意味が分かるだけでも「かなりのことが行えそう！」と感じませんか？

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前に！を伴って付くシート名（例：平均値の計算!A2）みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を３つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

１．コードのカラー

まず、Power Queryエディタ（以降、エディタ）で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード（自動記録）された値

こちらは、エディタ内でステップを追加した際に、ハードコード（自動記録）された値です

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

２．大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう！

３．ステップの引継ぎ

前述の１．で列のマージを行ったエディタ内には、下の画像のように２つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか？

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には＜一つ前ステップの内容＞を自動的に引き継いでいきます

４．参照ステップ作成

前述の３．ではエディタ内で＜一つ前のステップの内容＞を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ）カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ）カスタム列の作成

ⅰ）で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

＜まとめ＞

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

２．大文字と小文字の区別

３．ステップの引継ぎ

　上の３つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、３つの特徴の後には３．ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ）一旦、注文金額の平均値を計算➡参照ステップ

ⅱ）カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ）ⅱ）の処理はエクセルシート上で例えるなら、以下の画像のような処理です

　データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です　

　M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

　では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

記事を最後までお読み下さり、誠にありがとうございました

参考までに解説に使用したエクセルファイルを添付します

Mコードに慣れる1回目 23.02 KB 40 downloads

...

ダウンロード

にほんブログ村

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

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

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

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

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

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

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

目次

今回のポイント

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

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

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

＜まとめ＞

今回のポイント

ステップ名変更

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

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

　前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

　クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

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

今回は次のデータを使用します

担当者別に受注金額を管理する表です

　こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

Mコードに慣れる3回目_演習 17.64 KB 29 downloads

...

ダウンロード

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります！

演習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コードに慣れる3回目_完成版 23.61 KB 10 downloads

...

ダウンロード

にほんブログ村