【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画像のように型式変更が行われます

各行に日付テーブル作成

では、「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/

にほんブログ村

【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重にして[[連番]]にします

波括弧 / ｛｝による参照

波括弧 / {}の中には数字が入ります

波括弧 / {} に入れる最小の数字は0です

0から始まるのは、M言語が過去記事で紹介したように0をベースとしているからです

では、角括弧 / []と同じデータを使用して、波括弧 / {}の中に数字を入れてみましょう！

まずは波括弧 / {}に 0をいれてみます

➡　= 変更された型{0}

すると、1行目の「レコード」が参照されます

ちなみに上の画像は、レコードツールという表示になっています

次に、波括弧 / {}に 1をいれてみます

➡　= 変更された型{1}

こんどは2行目のレコードが参照されます

角括弧と波括弧の組み合わせによる参照

角括弧と波括弧の組み合わせでは、2種類の参照方法を紹介します

1つ目は、角括弧と波括弧を並列で組み合わせます

角括弧と波括弧の組み合わせにより、「1番目のレコード」の「連番列」の値を抽出します

➡ = 変更された型{0} [連番]

こちらは画面上をみると、リストでもレコードでもなく、値が参照されているのがわかります

こちらは、ドリルダウンを行った時と同じ内容です

2つ目は、波括弧 / {}の中に角括弧 / []を入れます

角括弧 / []の中には、次の画像のように「列名=列の中味」を入れ、列の中味を通じてレコードを参照します

➡ = 変更された型{[連番=1]}

すると、1行目のレコードが抽出されます

＜まとめ＞

　今回は、角括弧 / []と波括弧 / {}を使用して、エディタ内で参照作業を４つ行いました

１．角括弧 ➡リストを参照

２．波括弧➡レコードを参照

３．角括弧と波括弧の組み合わせ①➡値を参照

４．波括弧の中に角括弧を入力➡レコードを参照

　記事の冒頭で、Power Queryの「データ構造化」には４つの概念（値、リスト、レコード、テーブル）があると紹介しました

実際に手を動かして、上記の４つの作業を行ってみて、４つの概念について肌で感じて頂けると幸いです

　４つの概念を肌で感じた時には、Power Queryへの理解が一段と深まっているはずです

今回は、以上です

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

次回は、空のクエリから3種類の括弧を使用してリスト、レコード、そしてテーブルまで作成します

にほんブログ村