VBAで言えば、メソッドにあたるものです
M関数辞典はこちらから
文法
指定した行数分を先頭行から削除
Table.RemoveFirstN(テーブル名, 行数指定)
実際の使用例はこちらから
文法
テーブルに新たな「連番」の列を追加します
連番の開始番号や増分は調整できます
Table.AddIndexColumn(テーブル名, 列名, 開始番号, 増分)
実際の使用例
文法
先頭行をテーブルの見出し/ヘッダーにします
実際の使用例
文法
抽出条件と一致する行を選択します
Table.SelectRows(テーブル名, 抽出条件)
実際の使用例
➡M言語に慣れる_8回目～EACHを使いこなしてM関数作成～
VBAで言えば、メソッドにあたるものです
M関数辞典はこちらから
文法
リストの中から条件に一致するものを抽出
=List.Selct(リスト,条件)
実際の使用例
➡M言語に慣れる_8回目～EACHを使いこなしてM関数作成～
文法
累計などを集計する時に使用します。条件に合うリストを作成します
=List.FirstN(リスト,条件)
実際の使用例
【M言語は難しくない！】今回は使用方法に合ったM関数を、Power Queryエディタ（以降エディタ）上で検索する方法を紹介します
エクセル関数では、エクセルシート上で直接、関数を検索できます
実は、Power Queryでも同じような機能があります
エクセル関数と同じ様に検索した後に、そのまま使い回すことはできませんが、この機能を覚えておくととても便利です
まずは準備として、空のクエリを作成します
「データタブ」➡「データの取得」の順でクリックします
次に「その他のデータソース」を選択し、「空のクエリ」をクリックします
【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行で組み合わせたものです
上の②と③を組み合わせたものです
これらの４つの概念を頭に入れたところで、本格的な解説をはじめます
【M言語はむずかしくない！】 数式バーでよく見かけるコードの一つに、青字の「each」というコードがあります。青字ということは、システム予約語なので、数式バーに頻繁に登場します。ですので、「each」に慣れれば、自ずとM言語自体にも慣れ親しんでいけます。今回はこの「each」についての事例を数多く紹介します。
多くの事例を通じて「each」自体についての意味合いを掴み、M関数内でのeachの使い方に慣れていきましょう！
「each」とセットでよく出てくるコードに「アンダースコア / _」があります
「each」と 「アンダースコア / _」の２つは切っても切れない関係にあります
何故なら、「each」は「アンダースコア / _」を使用した関数を簡略したものだからです
ですので、今回は 「アンダースコア / _」についても解説します
但し、「each」と「アンダースコア / _」の詳細な内容は難解なので、「こういうもの」だと割り切って読み進めてください
そして、最後にM関数「List.Select」を「each」と「アンダースコア / _」を使い作成します
実際に手を動かしてみて「each」と「アンダースコア / _」に対する感覚を掴んでみてください
M関数の作成にあたっては、前回も行ったようにM関数の使用例を活用します！
まずは、「each」の理解の入り口として「each」の使用例を２つ紹介します
下の画像のように、エディタ内に「氏名」「部門」という列名の列があるデータがあるとします
こちらの部門列から「営業」をフィルタリングします
すると、数式バーは以下の表示になり、「each」が条件式に合うものに対して使われているのが分かります
前述のデータにて、今度は「氏名」の列から「苗字」を抽出します
使用するメニューは「変換」タブ内の「抽出 / 区切り記号の前のテキスト 」になります
区切り文字として使用する記号は「半角スペース」を使用します
すると、数式バーは以下の画像のような表示になり、「each」と「アンダースコア」(“BeforeDelimiter(“の後に表示)が使用されています
上の画像だと分かりずらいと思いますので、文字列に直して表示し直したものも確認しておいてください
= Table.TransformColumns(フィルターされた行, {{“氏名”, each
Text.BeforeDelimiter(_, ” “), type text}})
【M言語は難しくない】今回は、空のクエリにM関数を１つ入力するだけで複数シートを一気に結合できるようにします！今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます
Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います
今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します
１つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます
これで、シート間のバケツリレーの作業からは抜け出すことができます！
但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します
今回はM関数を入力した後に、1点だけ注意点があります
それは、循環問題です
複数シートを１つにまとめた内容/クエリが、M関数に反映されてしまいます
ですので、クエリを更新すると読み込み行数が倍になります
対策として、適用したステップに１つのステップを追加します
今回使用するデータには、シートが３つ含まれています
1シートが1か月分の出荷データになっています
各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります
次のファイルが今回、実際に使用するサンプルデータです
【M言語は難しくない】今回はデータをグループ化した際に、１から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります
コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります！
しかも知られざるグループ化機能を使えば、１つのM関数を入力するだけでできてしまいます
まさに魔法です
ぜひ、実際に手を動かしてこの魔法を体験してください！
今回のポイントは２つあります
グループ化する際に、よく選択される操作は「合計」などです
今回は「すべての行」という操作を選択します
この「すべての行」を選択して操作を行うと、グループ毎にテーブルが作成されます
連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます
今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します
M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります
今回は下の画像にあるデータの「部門」列をグループ化します
グループ化した後は、グループ毎に連番を振ります
以下が実際に使用するサンプルデータです
解説は、前述のデータをテーブル化し、エディタを開いたところから行います
エディタを開いたらまずは、グループ化を行います
グループ化は「ホームタブ」の「グループ化」メニューから行います
次に開いた画面では、前述のように操作を「全ての行」で設定します
・グループ化対象列 ➡ 「部門」列
・新しい列名 ➡ 「部門列」
・操作 ➡ 「すべての行」
・列 ➡ 空欄のままでOKです
上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています
【M言語は難しくない】今回は連番と波括弧：{}を組み合わせて活用して、前行を参照する仕組みを作成します。前行を参照する仕組みを作成したら、下の図のように「前行との差額」を計算します。
この処理はエクセルシート上であれば、四則演算の式を挿入すれば簡単に行えます
=（列名/N行目）-（列名/N-1行目）
但しセル単位/１つの値単位の操作になるので、M言語を使用しなければPower Queryでは行えません
M言語を使用すればできるといっても、とても難しい処理なのでは？
いえ、決してそんなことはありません！
エクセルシート上の行番号/レコードに相当する波括弧:{}を、連番/0,1,2・・・とうまく組み合わせて使いこなせば、簡単にできます
実際に手を動かして前行を参照した計算が行えるようになった時には、M言語の活用に大き自信を持てるようになっているはずです！
過去の記事で、丸括弧と波括弧を組み合わせてエディタ内のデータを参照する方法について解説しました
括弧を使用して参照する方法は、下のGIF画像のように＜前ステップ名 + 波括弧/レコード位置 + 角括弧/リスト>を組み合わせる方法でした
今回は、この「括弧の組み合わせて参照する仕組み」を、更に「連番 /0,1,2,3・・・」と組み合わせて活用します！
今回使用するデータは次の画像のデータです
上の画像のB列にある「株価」を使用して、計算を行います
各行にて、前行との株価の差額を計算します
【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、１つのセルにまとめます
今回のポイントは２つあります
1つ目は過去記事で紹介した「すべての行」によるグループ化です
この「すべての行」によるグループ化により、グループ別にテーブルを作成します
2つ目はリストの「値の抽出 / １つのセルにリスト化」機能の活用です
こちらは、実際の例で見てみましょう！
空のクエリで次の画像のように、２つのリストを作成したとします
上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます
上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます
「区切り記号」を指定した後は、下の画像のように１つのセルにリストの値が出力されます
今回使用するデータは、次の画像のデータです
名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を１つのセルに出力します
今回の解説は、使用データをエディタで開くところから始めます
上の画像にて、黄色の印を付けた「グループ化」をクリックします
なお、事前に「注文日」の列は削除しておきます
グループ化の内容は次の画像のように指定します
・グループ化項目：名前の列をグループ化します
・新しい列名：詳細
・操作：前述のように「すべての行」を指定します
・列：空欄のままでOKです
上記のように指定してOKボタンを押すと、エディタ画面は次のようになります
グループ化された名前毎に、テーブルが作成されています
次はグループ毎に、上の画像の商品列の内容をリスト化します
前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます
カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます
次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です
今回はテーブルではなく、各テーブル内の商品リストを出力します
リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます
これで、商品リストが各テーブル毎に出力されます
各リストにカーソルをあてると「リストの中味」が次の画像のように見れます
こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から１つのセルにリストを出力します
「値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください
記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています
仮に変換対象のデータが1列のケースも解説します
この場合は、一旦、カスタム列作成画面で「ダミー列」を追加します
こうすることで「グループ化」機能を前述のようにうまく活用することができます
今回は複数の行の内容を、１つのセルに出力する方法を解説しました
ポイントは２つあり、まず1つ目はグループ化において「すべての行」を指定することです
この「すべての行」を指定すると、グループ毎にテーブルが作成されます
グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます
2つ目のポイントは、各リストの内容を「値を抽出する」機能にて１つのセルにリストを出力することです
今回紹介した方法は、それほど使用頻度は高くないと思います
ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います
特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです！
ぜひ、実際に手を動かしてみて試してみてください
記事を最後まで見て頂き誠にありがとうございました
参考までに今回使用したファイルを添付します
次回は、M関数のText関数について２つ事例を紹介します
【M言語は難しくない】今回は、TEXT関数の中から、2つの事例を紹介したいと思います。通常のエクセル関数と同じ様に、M言語でも文字列を操作する関数は多数あります。興味のある方はぜひ、こちらのMicrosoft社のページも参照してみてください
Text.Contains関数は、ある文字列の中に指定した文字列があるかとうかを判定し、「TRUE」か「FALSE」で返す関数です
以下の一文は、Microsoft社のページからの抜粋になります
Text.Contains(“Hello World”, “Hello”)
上の ように記述した場合には、「TRUE」を返します
次もMicrosoft社のページからの抜粋になりますが、この場合は「FALSE」を返します
Text.Contains(“Hello World”, “hello”)
ちなみに、
M言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です
上の画像のデータから、Text.Contains関数で文字列「0」を含む行を判定し、「TRUE」もしくは「FALSE」を返してみます
まず、「列の追加」タブからカスタム列・作成画面を開きます
こちらの画面でカスタム列の式に「Text.Contains」と入力します
上のGIF画像のように「Text.Con・・・」と入力する途中で、該当の関数が出てきますので、Tabキーで確定します
次にText.Contains関数の丸括弧の中を、以下の画像のように確定します
すると、エディタ内に「0」が含まれる行を判定した列が追加されます
