2017/02/19

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 取得と変換で共通の見た目、操作性を持つウィンドウとして起動します。

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

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


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

M言語の特徴

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

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

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

数式バーはステップ1つの数式のみ確認できますが、[詳細エディター] を使うことで、記録されているすべてのステップの数式の確認が可能です。行の絞り込みや、列の削除や追加の操作は、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 の一連の流れの中で、機能的にできることを把握し、適切な場面で、適切な機能を使えるようになりたいですね。

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

4 件のコメント:

  1. 沼口さん
    いつも参考にさせていただいています。石和です。

    PowerQueryの機能には、MSクエリで可能だった
    セルの値を使ったパラメータークエリのような機能はないのでしょうか?
    大量のデータベースから、セルに指定した条件でデータが取り出せて便利だったのですが

    返信削除
    返信
    1. お世話さまです。

      パラメーターの機能ですね。絞り込み条件で、明示的に数値や文字を指定するだけではなく、事前にパラメーターを作成し、それを条件に指定することで、条件を可変にできるようになりました。
      パラメーターはリスト型でなければいけないため、パラメーターとして使いたい条件のセット(セルの集まり)がテーブルの場合はリストに変換するクエリを作成し、そのクエリをパラメーターとして指定することができます。
      [クエリを編集] にある [パラメーターの編集] でパラメーターの選択を変更し条件を変えることができます。
      上記のようなことをしたいのですよね?

      削除
    2. Power BI Desktop だと期待通りの手順で、パラメーターを変更し、絞り込み条件を変えることができるのですが、Excel 取得と変換だと、パラメーターの設定はできますが、操作性がちょっと違いますねぇ。コレジャナイ感が漂っています。もうちょっと調べてみますね。

      削除
  2. この件に関する投稿をしました。ご参照ください。
    https://road2cloudoffice.blogspot.jp/2017/03/power-query.html

    返信削除

Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。