M言語 Power Query Formula Language

先日、Power BI 勉強会#3 で「M言語 Power Query Formula Languageとは？」というお話をする時間をいただきました。
冒頭「今回初めて参加された方はどのくらいいらっしゃいますか？」と伺ったところ、結構な方が初めての参加で、これから Power BI を勉強しよう、という人が多かったので、スライドなしで、Power BI の概要を前半の時間を使ってご紹介しました。
よって当初予定の用意したスライドをかなりの速足でご紹介してしまい、大変申し訳ないと感じています。

勉強会スライド　
https://doc.co/UY3RDb

昨日お伝えしたかった内容を再度まとめておきたいと思います。

データを取得し、準備するETL機能とクエリ エディター

Power BIは、Power BI Desktop や Power BI サービス(Web)などのアプリケーションやサービスを総称した名称ですが、Excel にも Power BI アドインというものがあり、その中でも Power Query は Excel 2013 ではアドインとして、Excel 2016 では標準機能の「取得と変換」となって利用可能になっています。
この Power Query の機能はいわゆる ETL(Extract, Transform, Load)機能と呼ばれる、データを抽出し、分析しやすいよう変換し、保存する、という役割を受け持つもので、Power BI Desktop もほぼ同じ機能を持っています。

その機能を実現するのが「クエリ エディター」です。

クエリ エディターは、Power BI Desktop および Excel の Power Query アドインまたはExcel 2016 取得と変換で共通の見た目、操作性を持つウィンドウとして起動します。

マクロ記録のようにクエリ エディターの操作を記録

このクエリ エディターは、そこで行われた１つ１つの操作をクエリの設定ウィンドウの [適用したステップ] として記録していきます。そのときの操作内容を記録する言語が M 言語と呼ばれたり、Power Query Formula Language と呼ばれます。
記録されているステップ名を選択すると、プレビューグリッドにはその操作の結果が表示され、プレビューグリッド上部の数式バーに M言語で記述された数式が表示されます。

操作手順を記録しているので、いまやった操作を「取り消したい」場合は、適用したステップに表示されているステップを削除すると、前の状態に戻ることができます。

M言語の特徴

クエリ エディターで利用する(記録される)M言語には、ある特徴があります。クエリ エディターでは、プレビューグリッドに表示された表やテーブルの操作が記録されるため、記録される M言語の関数は「テーブル」を扱ったものになります。
M言語による数式は、Power Query 関数 (もしくは M言語関数)を使いますが、その関数の表記は以下が基本形です。

クラス.関数(参照するステップ (, その他の引数))

参照するステップの多くは「直前の」ステップになります。クエリ エディターの操作は表を対象することが多いため、クラスの部分が Table の関数が多く使われます。

数式バーはステップ１つの数式のみ確認できますが、[詳細エディター] を使うことで、記録されているすべてのステップの数式の確認が可能です。行の絞り込みや、列の削除や追加の操作は、Table.○○○という関数が使われています。

Tableに関連する関数のパラメーターとして、日付に関する関数や、文字に関する関数などが使われます。Excel のワークシート関数の数式と同じように、関数のパラメーターとして他の操作を行う関数を組み合わせることができます。

たとえば、以下の数式は、フィルターされた行、というステップ名(および変数名)で、Table.SelectRows という関数を使って、行の絞り込みをします。絞り込みの対象は、直前のステップの[変更された型]というステップの結果のテーブルで、条件は、[日付]列の各行の日付データが Date.IsInCurrentYear 関数を使って「今年」であるものを絞り込んでいます。

フィルターされた行 = Table.SelectRows(変更された型, each Date.IsInCurrentYear([日付])),

詳細エディターで確認できるクエリは、複数の操作ステップを let ～ in で括って、in の直後に最終ステップ名(変数)を記述します。そのステップの結果がプレビューグリッドに表示されます。

詳細エディターとM言語を使ってゼロから記述するのか？

結論から言えば、それはハードルが高く、難しいです。
現時点で詳細エディターは、開発環境としての機能は皆無です。インテリセンスのような入力支援もなく、ヘルプ機能や、エラーチェックもないに等しい状態です。
苦労して動くものを作ったとしても、業務内における引継ぎを考えるとお勧めできるものではありません。

現状は、クエリ エディターで行った操作の記録として再利用し、修正が必要であれば最小にとどめることが肝要だと感じています。

ただし、カスタム関数と呼ばれるクエリを使うと便利な場合が出てきます。その作成では詳細エディターでM言語を使わざるを得ません。

カスタム関数を作る

関数なので、引数を宣言します。記述方法は let ~ in の直前に引数を (param1 as text)=> のような記述で設定します。文字列の引数を param1 という名前で定義して、let ~ in の中の数式に渡します。このように引数を設定したクエリは「クエリ関数」になり、他のクエリから参照することができます。

まとめ

空のクエリからM言語を使ってゼロからクエリを記述する、、、というクエリの活用はハードルが高く、エディターの支援機能などを見ても、まだ万人向けとは言えません。多くの操作はクエリ エディターのリボン コマンドから可能で、毎月のアップデートで機能追加されています。
また、この後工程での「分析」のフェーズでも、データの「整理」が可能です。特に「日付」に関しては、クエリ エディターでやるべきか、分析のフェーズで DAX のタイムインテリジェンス関数やカレンダーテーブルですべきか、といった判断が必要になるケースもあります。
データを収集し、分析し、レポートする、という Power BI の一連の流れの中で、機能的にできることを把握し、適切な場面で、適切な機能を使えるようになりたいですね。

以上、昨日お伝えしたかったポイントをまとめてみました。