目次
文法
３つの数字（年、月、日）から日付を作成
=#date(年,月,日)
実際の使用例
➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成
文法
日、時間、分、秒の４つの数字から期間値を作成
=#duration(日,時間,分,秒)
実際の使用例
➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成
文法
日付リストを指定回数分、指定期間単位で作成
=List.Dates(開始日,指定リスト数,期間単位)
実際の使用例
VBAで言えば、メソッドにあたるものです
文法
リストの中から条件に一致するものを抽出
=List.Selct(リスト,条件)
実際の使用例
【M言語は難しくない！】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう！。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。
例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです
ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします
今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません
あくまで、Power Queryエディタ（以降エディタ）にて、既に数式バーに記録されているM言語を修正するだけです
マクロ作成で言えば、記録マクロを修正するような内容です
記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです！
実は、エディタのステップ名は自分好みに修正していけます
今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります
前々回、ステップをスキップして参照することで「参照ステップ」を作成しました
前々回は平均値を参照しましたが、今回は「最高値」を参照します
クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします
一度、数式バーにてダミー値でフィルタリングを行います
ダミーで設定をした箇所に、前述の参照ステップを組み入れます
今回は次のデータを使用します
担当者別に受注金額を管理する表です
こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします
演習の中で、こちらの表にデータを新たに追加したりします
今回のポイントと使用するデータを確認したところで、本格的な演習に入ります！
まず、表をテーブル化してエディタを開きます
表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします
エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います
下の画像の「変更された型」を「DATA」に変更しましょう
クエリ名を変更するのと同じ要領でステップ名も変更できます
「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます
なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります
この点は後述します
【M言語は難しくない！】今回はカスタム列の隠れた裏技を使用して、特殊なテンプレートを作成します。今回は次の画像のように、「1対多」の関係の結合を「コードを1つ追加する」だけでサクッと行います
この処理は、従来であればVBAなどを覚えないとできない処理でした
M言語の仕組みを使えば、カスタム列・作成画面にて本当にサクッと作成できます
M言語の魅力のうちの一つといってもいい処理かもしれませんね！
次の画像のように「担当者名のリスト」と「勤務日のリスト」から、各担当者別の勤務表を作成します
上の画像の右側/E列とF列の表のように、担当者1人に対して、複数の勤務日を結合します
勤務表は、最終的には勤務日の右に「勤務開始時刻」や「勤務終了時刻」などを追加して使用するイメージです
解説は、シート上の「担当者名のリスト/列」と「勤務日のリスト/列」から次の２つのクエリを作成した上で、Power Queryエディタ（以降エディタ）で操作を開始するところからはじめます
・担当者名のリスト ➡ Namesクエリ
・勤務日のリスト ➡ TimeTable
次のファイルに含まれる演習用の元データは、クエリを作成する前の状態になっています
今回の「カスタム列」では、前述の通り「1対多」の関係で結合を行います
次の画像のように「列」ではなく「テーブル」を結合します
この「テーブル」を結合するの点が、今回の解説の最大のポイントになります
今回作成する内容とポイントを確認したところで、本格的な解説をはじめます
前述の通り、解説はエディタ内に２つのクエリがある状態から開始します
・担当者名/列のリスト ➡ Namesクエリ、・勤務日/列のリスト ➡ TimeTable
過去の記事で解説した通り、「データの構造化」が行われた結果、データ形式の変更が必要な箇所が1点でてきますので、まずはそちらを変更します
データ形式の変更は、上の画像の左上の黄色の印から行えます
上のGIF画像のように、日付を指定した後は次の画面が開きます
「現在のものを変換」をクリックすれば、下のGIF画像のように型式変更が行われます
今回はM関数を使用して、事前にエクセルシートに記入した複数の値を基にしてフィルタリングを行う方法を解説します
M関数に慣れるきっかけになったら幸いです
今回使用するM関数は「List.Contains」という関数です
ちなみにM言語では、大文字と小文字を認識するのでContainsのCの入力には注意が必要となります
今回使用するデータは、次の画像のデータです
＊下の画像では業種IDが4番までしか写っていませんが、実際は12番まであります
上のデータの「業種ID」をいくつかエクセルシート上にフィルタ項目として指定し、指定した業種IDのレコードのみをシートに読み出したいと思います
上のGIF画像では、事前に「２」「４」をシートに読込んである状態から、フィルタ項目に５を足すことで「２」「４」「５」を読み込みました
今回の処理のポイントは、一度ダミーの値にてフィルタリングしてから、作成されたコードにM関数を組み込むことになります
では、解説を本格的にはじめたいと思います
まずは、前述の元データをPower Queryエディタ（以降エディタ）に読込ます
次に、新たにできたクエリを「参照」して更に新たなクエリを作成しておきます
参照して作成したクエリの「業種ID・列」にて、次の画像のように適当な値をフィルタリングしておきます
すると、数式バーが次の画像のようになっているはずです
エディタ上でフィルタリングを行うためのリストは、エクセルシート上にある前述のフィルタ項目を「ドリルダウン」して作成します
まず、エクセルシート上の業種IDを指定したフィルタ項目をエディタ上に読込みます
次に上の画像の見出しの箇所/フィルタ項目で、右クリックをします
「ドリルダウン」という項目が出てくるので、こちらをクリックします
すると、次の画像のようにエディタ上にリストが作成されています
＊下の画像では作成したリストの名前を「Filter」にかえてあります
本題に入る前に、M関数の「List.Contains」がどういうものかを確認しておきましょう！
上の図は、M関数「List.Contains」の使用例です（空のクエリから見れます）
2番目の引数の「３」が1番目のリストの中に含まれていれば、TRUEを返します
つまり、今回は「List.Contains」を使用することでTRUEが返ってきた行を表示する形にすればいいということです
ではもう一度、前述のダミーの値でフィルタリングした後の数式を確認してみましょう！
上の画像の「each」に続く「」の中は、「List.Contains」で置き換えます
List.Containsの書き方は次の通りになります
=List.Contains(リスト,値)
今回は上記のリストについては、前述のドリルダウンにより作成した「Filter」を指定し、値には「業種ID列/[業種ID]」を指定します
上の画像のように数式を指定すれば、事前に「フィルタリング項目」で指定してあった業種IDの行のみが表示されます
今回はドリルダウンの仕組みを使用して、エクセル上で複数指定した値にてダイナミックにフィルタリングを行う方法を解説しました
M関数としては、「List.Contains」を使用しました
一度、ダミーの値でフィルタリングしておいた箇所に「List.Contains」を組み入れただけなので、無理なくM関数の活用が行えたと思います
この記事がM関数に慣れるきっかけになれば幸いです
最後まで記事を読んで下さり、誠にありがとうございました
こんにちは、Excellent仕事術のガッツ鶴岡（@atsushi1039）です
前回は「例からの列」のメニューを使用して、変換パターンを１つ自作しました
こちらは１つ例を示して、氏名から「姓」だけ、もしくは「名前」だけを分解する変換パターンでした
下の画像が、変換前の氏名でした
そして「例からの列」機能を使い、上の画像を下の様に分解しました
では、今回は変換内容の２つの例示を通じ、作成したい変換パターンをPower Queryに汲み取らせた上で、変換パターンを確定する方法を紹介します
この「２つの例示を行うパターン」が使いこなせるようになると、列のデータが不規則でも、ノーコードにてデータ成型が一括で行えるようになります
解説に使うデータは前回と同じです
今回変換するのは、下の画像の「部門」の列です
こちらは部門コードが4~5桁になっており、桁数が不揃いになっています
この部門コードを3桁で統一します
まずは、例示の1回目を行います
1行目の「ABC1」を「ABC」にて例示すると下の図のようになります
やりたいことは列全体を3桁に揃えることでしたが、3桁になったのは例示した行も含めて2行だけでした
・ABC1 ➡ABC
・ABC10 ➡ABC
他の行は次のような状態です（一部省略）
・BBC2 ➡BBC2
・ABB33➡ABB33
全く変化が起こっていませんので、画面左上の変換式をチェックしてみます
要は「１」の前の文字を切り取る変換式が出来ていました
では、次に下のGIF画像のように「１」を含まない行にて、2つ目の例示を行いましょう
2つ目の例示を追加したら、全て3桁に揃いました
では、変換式を確認してみましょう！
「Text.Start([部門],3)」となっています
こちらは、「部門」の列の各行の文字から3文字を左から切り取ったという意味になります
つまり、２つの例示を通じて「3桁」にしたいという意図をPower Queryが汲み取った形になっています
あまり使う機会がないかもしれませんが、「列」と「列」の計算もこの「例からの列」で行えます
「単価」と「個数」を乗じた計算結果を２つ例示してみます
今回は、1行目と2行の計算結果、1800x 7 = 「12600」と3800 x 3 = 「11400」を入力してみます
すると、２つの計算結果を汲み取ってくれて、列の全行に計算結果を入力してくれます
今回は複数の例示から、変換パターンを汲み取らせて変換するパターンを行いました
前回の冒頭にも紹介しましたが、複数の列を組み合わせて「文章」も作成することもできます
但し、大量のデータがある場合には、変換ミスが起こっていても気づかないケースもありうります
なるべく複雑な変換は行わないようにしましょう！
そして、画面左にある「変換式」はなるべくチェックするようにしましょう！
最後に、参考までに今回の解説で使用したデータと完成版を添付します！
次回は、エディタを開かないまま「データソース」を変更する処理を解説します
