【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対多」の関係の結合を「コードを1つ追加する」だけでサクッと行います

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

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

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

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

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

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

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

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

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

・勤務日のリスト ➡ TimeTable

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

目次

今回のポイント

データ形式の事前調整

各行に日付テーブル作成

作成テーブルの展開

条件式の追加による調整

シートへの読み込み処理

＜まとめ＞

今回のポイント

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

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

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

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

データ形式の事前調整

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

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

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

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

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

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

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

【M言語は難しくない】Power Queryを扱っていると、3種類の括弧が出てくるのが分かります。 「M言語が難解」と感じる理由の一つは、この３種類の括弧が組み合わせられて出てくるからです。

・丸括弧 / ()

・角括弧 / []

・波括弧 / {}

特に下の２つの括弧の[]と{}は、初めて見たときは意味合いが全く不明です

実は、この２つの括弧がM言語を使いこなすための重要な鍵です

M言語を学ぶ目的の一つは、行列単位での一括処理からの脱却です

シート上でセルを扱うように、Power Queryエディタ上でも１つの値を扱いたいのです

少し、シート上でのセル操作についてお話したいと思います

セル名は例えば「A８」であれば、Aは列名で８は行番号です

この列名と行番号を組み合わせれば、望みのセルにたどり着きます

実は角括弧：[]はシート上の列名に、そして波括弧：{}は行番号に相当します

今回はこれらの括弧に、次の２つの方法を通じて慣れて頂くとともに、それぞれの意味合いを掴んて頂きたいと思います！

なお、解説は今回を1回目として、2回に分けて行います

・既存のクエリから角括弧 / []と波括弧 / {}を使い、１つの値を参照する　➡1回目

・空のクエリから3種類の括弧を使って、テーブルを作成する　➡2回目

この３つの括弧の意味合いを掴んだ時には、M言語への苦手意識が薄れているはずです！

目次

今回のポイント

括弧を使ってデータ参照

＜まとめ＞

今回のポイント

今回の解説にあたっては、そもそも、Power Queryとは何なのか？という点がとても重要です

エクセルはもともと表計算ソフトであり、データベースではありません

Power Queryは、エクセルをデータベースとして使用できるようにするツールです

ですので、エディタ内ではデータベースとして使用するための「データの構造化」が行われています

この構造化が「Power Queryは何か？」を理解するためのキーワードです

まずは、構造化には4種類の概念があることを常に頭に入れておきましょう！

①値

値の種類には、主に次のようなものがあります

・1/ 整数（数値）

・b / テキスト（文字列）

・2021/03/04（日付）

今回の解説で使用する値の概念は、もっと本源的なものです

例えば、１は数字ですが、データベース上は２つの型式になりうります

今回解説する値の概念に照らすと、１は整数でもない文字列でもない、ただの「１」です

②リスト

①の値が、列方向に順次並べられたものです

通常の列とは違います

その点は後で解説します

③レコード

行方向に、複数の②のリストを1行で組み合わせたものです

④テーブル

上の②と③を組み合わせたものです

これらの４つの概念を頭に入れたところで、本格的な解説をはじめます

括弧を使ってデータ参照

今回は、３つの列から構成される、次の画像のデータを解説に使用します

上の画像の表から括弧を使って、様々な参照作業を行うわけですが、まずは参照作業を行い易くします

ちなみに、この章では角括弧 / []と波括弧 / {}による参照しか行いませんので予めご了承ください

　参照作業を行い易くする為に、下のGIF画像のように数式バーのマークをクリックし、数式バーの内容を前ステップ名で確定させてます

これで数式バーにて、前ステップ名の次に括弧をつなげていくだけで参照作業ができるようになります

角括弧 / []による参照

角括弧 / []の中には、基本的には列名のみが入ります（列名以外も入れるパターンは後述）

では、数式バーに次の内容を入力してみます

➡　= 変更された型[連番 / 1列目]

すると、次のGIF画像 のように、前述のデータの1列目が参照されます

下の画像の赤丸の箇所に注目してくみてください

リストとして出力されているのが良く分かります

　元の「列名」である「連番」の記載がどこにもなく、データ形式「整数」の表示もありません

変換タブでリスト変換した時と同じ画面になっています

前ステップの列の内容をそのまま参照する場合には、角括弧 / []を2重にして[[連番]]にします