Mから始めよう #3 ～Power BIでM式を使う～

この記事は、Microsoft Power BI Advent Calendar 2020の14日目の記事となります。

前回の記事はこちら。

• Mから始めよう #1 ～Power BIでM式を使う～
• Mから始めよう #2 ～Power BIでM式を使う～

Mとは

Power BIでMと言われているのは、Power Query Formula Languageが正式な名称です。M式、M数式、M言語、様々な呼び方をされています。由来をきちんと書かれたものが見つからないのですが、私はデータをマッシュアップ（mash-up)するので、Mと呼ばれていると解釈してます。
　通常、letで始まり、inで値を返す関数の形で使われます。GUIの操作で行う以上のことが、詳細エディターを開いて直接式を書くことで可能になります。詳細は、Microsoft Docsに書かれていますが、日付関数のことを「データ関数」と書かれている箇所もあったり（dateとdataの混同？)、結合関数を「コンバイナ関数」と書かれていたり、誤訳や特殊な用語に訳されたりして全体的に読みにくいです。英語と比べながら読むといいかもしれません。ページの右上に、「英語で読む」ボタンで切り替えられます。
　M式について、さらに詳しく知りたい場合は、Ben Gribaudoのblogに、わかりやすく書かれています。英語ですが、サンプルが多いので、自動翻訳と合わせても読みやすいです。
　少し動かしてみましょう、Power BIを起動し、データの取得から「空のクエリ」を選択し、「詳細エディッター」を開きます。


詳細エディターの中を全部消し、以下のようなコードを入力してみてください。

let
    x = 1 + 1,
    y = x + 2,
    z = y + 1
in
    z

　式が続く場合に末尾に「,」がついていることに注意してください。最後に、inの後に続く値が返されます。完了ボタンを押すと、inに後に書かれたzの値が返されます。この結果は、5となります。この時、左側の適用したステップが以下のようになっています。

　マウスで、x、y、zそれぞれをクリックすると、途中の結果が確認できます。
　式は、上から順番に実行されているように見えますが、実際は依存関係の順序で実行されます。つまり、以下のような書き方をしても、結果は同じです。

let
    z = y + 1,
    y = x + 2,
    x = 1 + 1
in
    z

　結果は同じ5ですが、右側の「適用ステップ」の表示は正しく表示してくれません。
　let式は、入れ子にすることもできます。

let
    x = 1,
    y =
        let
            x = 10
        in
            x,
    z = x + y
in
    z

　入れ子になったletの中で定義された変数は、外の変数に影響しません。表示される数字は、11です。
　関数を記述することもできます。以下の例では、SecretFormulaという関数を定義して使用しています。

let
  Input1 = 10,
  Input2 = 15,
  Input3 = 30,
  SecretFormula = (a, b, x) =>
    let
      Output = x * (a + b),
      OutputDoubled = Output * 2
    in
      Output,
   Result = SecretFormula(Input1 , Input2, Input3)
in
  Result

　また、データの持ち方も、以下のような形があります。

リスト

let
    list1 = { 1, 2, 3, 4, 5 }
in
    list1

レコード

let
    record1 = 
        [ 
            A = 1,  
            B = 2,  
            C = 3 
        ]
    
in
    record1

テーブル

let
    table1 = 
        #table(
            {"a", "b", "c"}, 
            {
                {1, 2, 3},
                {4, 5, 6},
                {7, 8, 9}
            }
        )
in
    table1

　M式の変数には、日付、数値、文字のような単一の値をもつプリミティブ型の値と、テーブルや、レコード、リストという形式があることに注意してください。
　また、変数名は、日本語にしたり、間にスペースを入れることができます。詳細エディター上では、スペースなしの場合は、LatestDay、空白を入れる場合は、#"Latest Day" のような表記になりますが、「適用したステップ」での表記が見やすくなります。ただし、一般のプログラム言語と異なり、変数の値は、書き換えできませんので、使い方に注意してください。
　更に知りたい方は、Ben Gribaudo の blogをどうぞ。

M関数の種類

M関数は、Microsoft Docsでは、以下の通り分類されています。

カテゴリ	説明
データへのアクセス関数	データにアクセスし、テーブルの値を返します
Binary 関数	バイナリ データの作成と操作
コンバイナ関数	結合関数。戻り値は１つの値
比較関数	等価性をテストし、順序を決定
日付関数	date 値、datetime 値、および datetimezone 値の日付部分の作成と操作
DateTime 関数	datetime 値と datetimezone 値の作成と操作
DateTimeZone 関数	datetimezone 値の作成と操作
Duration 関数	期間の値の作成と操作
エラー処理	さまざまな詳細レベルの診断トレースが返され、エラー レコードがスローされます
Expression 関数	M コードの構築と評価
関数の値	他の M 関数の作成と呼び出し
List 関数	リスト値の作成と操作
Lines 関数	テキストのリストと、バイナリおよび 1 つのテキスト値との間で変換
論理関数	論理値 (つまり、true または false) の作成と操作
Number 関数	数値の作成と操作
レコード関数	レコード値の作成と操作
置換関数	特定の値を置き換えるために、ライブラリ内の他の関数によって使用
分割関数	テキストを分割
テーブル関数	テーブル値の作成と操作
テキスト関数	テキスト値の作成と操作
Time 関数	時間値の作成と操作
Type 関数	型の値の作成と操作
Uri 関数	URI クエリ文字列の作成と操作
Value 関数	値を評価したり、値に対して操作

M式の整形

　前回作ったpbixファイルを開いて、M式を確認すると、

WHO-COVID-19-candle

let
    ソース = Csv.Document(Web.Contents("https://covid19.who.int/WHO-COVID-19-global-data.csv"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"Date_reported", type date}, {"Country_code", type text}, {"Country", type text}, {"WHO_region", type text}, {"New_cases", Int64.Type}, {"Cumulative_cases", Int64.Type}, {"New_deaths", Int64.Type}, {"Cumulative_deaths", Int64.Type}}),
    削除された列 = Table.RemoveColumns(変更された型,{"Country_code", "WHO_region", "Cumulative_cases", "Cumulative_deaths"}),
    #"名前が変更された列 " = Table.RenameColumns(削除された列,{{"New_cases", "新規感染者数"}}),
    グループ化された行 = Table.Group(#"名前が変更された列 ", {"Date_reported"}, {{"新規感染者数", each List.Sum([新規感染者数]), type nullable number}}),
    追加されたカスタム = Table.AddColumn(グループ化された行, "WeekOfYear", each Date.Year([Date_reported]) * 100 + Date.WeekOfYear([Date_reported])),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "DayOfWeek", each Date.DayOfWeek([Date_reported])),
    追加された条件列 = Table.AddColumn(追加されたカスタム1, "Open", each if [DayOfWeek] = 0 then [新規感染者数] else 0),
    追加された条件列1 = Table.AddColumn(追加された条件列, "Close", each if [DayOfWeek] = 6 then [新規感染者数] else 0),
    グループ化された行1 = Table.Group(追加された条件列1, {"WeekOfYear"}, {{"Open", each List.Sum([Open]), type number}, {"Close", each List.Sum([Close]), type number}, {"High", each List.Max([新規感染者数]), type nullable number}, {"Low", each List.Min([新規感染者数]), type nullable number}})
in
    グループ化された行1

　という感じで、1ステップ1行で書かれています。このままだと、読みにくいので、手作業で整形します。左の変数名も、適宜直しながら作業を行うと、その次の行の変数名も直さなければなりませんので、括弧の位置が正しいかなどを確認しながら進めると、以下のようになります。私は、Visual Studio Codeで作業して、詳細エディターに貼り付けました。

WHO-COVID-19-candle整形後

let
    ソース = 
        Csv.Document(
            Web.Contents("https://covid19.who.int/WHO-COVID-19-global-data.csv"),
            [Delimiter=",", 
            Columns=8, 
            Encoding=65001, 
            QuoteStyle=QuoteStyle.None]
        ),
    昇格されたヘッダー数 = 
        Table.PromoteHeaders(
            ソース, 
            [PromoteAllScalars=true]
        ),
    Date_reportedを日付に変更 = 
        Table.TransformColumnTypes(
            昇格されたヘッダー数,
            {
                {"Date_reported", type date}, 
                {"Country_code", type text}, 
                {"Country", type text}, 
                {"WHO_region", type text}, 
                {"New_cases", Int64.Type}, 
                {"Cumulative_cases", Int64.Type}, 
                {"New_deaths", Int64.Type}, 
                {"Cumulative_deaths", Int64.Type}
            }
        ),
    削除された列 = 
        Table.RemoveColumns(
            Date_reportedを日付に変更,
            {
                "Country_code", 
                "WHO_region", 
                "Cumulative_cases", 
                "Cumulative_deaths"
            }
        ),
    名前が変更された列 = 
        Table.RenameColumns(
            削除された列,
            {
                {
                    "New_cases", 
                    "新規感染者数"
                }
            }
        ),
    日付ごとにグループ化 = 
        Table.Group(
            名前が変更された列, 
            {
                "Date_reported"
            }, 
            {
                {
                    "新規感染者数", 
                    each List.Sum([新規感染者数]), 
                    type nullable number
                }
            }
        ),
    WeekOfYearを追加 = 
        Table.AddColumn(
            日付ごとにグループ化,
            "WeekOfYear", 
            each Date.Year([Date_reported]) * 100 + Date.WeekOfYear([Date_reported])
        ),
    DayOfWeekを追加 = 
        Table.AddColumn(
            WeekOfYearを追加,
            "DayOfWeek", 
            each Date.DayOfWeek([Date_reported])
        ),
    //
    // もっとも古い日付と、もっとも新しい日付を取得
    EarliestDay = List.Min(DayOfWeekを追加[Date_reported]),
    LatestDay = List.Max(DayOfWeekを追加[Date_reported]),
    //
    Openを追加 = 
        Table.AddColumn(
            DayOfWeekを追加,
            "Open", 
            each 
                if [Date_reported] = EarliestDay or [DayOfWeek] = 0 then 
                    [新規感染者数] 
                else 
                    0
        ),
    Closeを追加 = 
        Table.AddColumn(
            Openを追加, 
            "Close", 
            each 
                if [Date_reported] = LatestDay or [DayOfWeek] = 6 then 
                    [新規感染者数] 
                else 
                    0
        ),
    //
    WeekOfYearでグループ化 = 
        Table.Group(
            Closeを追加,
            {"WeekOfYear"}, 
            {
                {
                    "Open", 
                    each List.Sum([Open]), 
                    type number}
                , 
                {
                    "Close", 
                    each List.Sum([Close]), 
                    type number
                }, 
                {
                    "High", 
                    each List.Max([新規感染者数]), 
                    type nullable number
                }, 
                {
                    "Low", 
                    each List.Min([新規感染者数]), 
                    type nullable number
                }
            }
        ),
    // WeekOfYearの頭4桁の年の最初の週の1日目を取得(2020年は2019年12月29日) 
    // それにWeekOfYearの下二けたの数字に7をかけた日付を加える
    Dateを追加 =
        Table.AddColumn(
            WeekOfYearでグループ化,
            "date", 
            each 
                Date.AddDays(
                    #date(Int32.From([WeekOfYear] / 100),1,1),
                    Date.DayOfWeek(
                        #date(Int32.From([WeekOfYear] / 100),1,1)
                    ) * -1 + (Number.Mod([WeekOfYear],100) - 1) *7
                )
        ),
    Dateを日付に変更 =
        Table.TransformColumnTypes(
            Dateを追加,
            {
                {"date", type date}
            }
        )
in
    Dateを日付に変更```

　「//」に続く部分はコメントになります。「/」「/」でコメントを囲むこともできます。
　前回の残された課題にも対応しています。一番最初の日と、一番最後の日をそれぞれOpenとCloseに入れる処理は、まず、最初と最後の日を変数に入れます。

    // もっとも古い日付と、もっとも新しい日付を取得
    EarliestDay = List.Min(DayOfWeekを追加[Date_reported]),
    LatestDay = List.Max(DayOfWeekを追加[Date_reported]),

　つぎに、Openの値を設定する部分で、DayOfWeekの条件に加えて、Date_reportedがEarlistDayと同じだった場合も値を設定するよう条件を加えます。Closeの場合は、LatestDayで条件を設定します。

            each 
                if [Date_reported] = EarliestDay or [DayOfWeek] = 0 then 
                    [新規感染者数] 
                else 
                    0

　また、X軸を日付にするために、dateの列を作成する処理を最後に書いてます。これは、グループ化の時にうまく残せば、わざわざ計算する必要はないのですが。

　これで、以下のようなグラフが出来上がりました。

　移動平均線はまだ作ってませんでしたね。1カ月と3カ月、6カ月の3本の移動平均線を作成します。これは、Mでやるとあまりに手間がかかるので、さっくりDAXで書いてしまいましょう。以下のようにメジャーを3つ作成します。

　メジャーの式は、tentomushiさんのPower BI -移動平均(Moving Average)の計算／レポートの設定方法をさくっと流用させていただきました。

1カ月移動平均線

１カ月移動平均線 = 
-- 4 Weeks
var interval = -28
RETURN
    IF (
        NOT ISBLANK(SUM('WHO-COVID-19-candle'[Close])),
        DIVIDE(
            CALCULATE(
                SUM('WHO-COVID-19-candle'[Close]),
                DATESINPERIOD(
                    'WHO-COVID-19-candle'[date],
                    LASTDATE('WHO-COVID-19-candle'[date]),
                    interval,
                    DAY
                )
            ),
            CALCULATE(
                COUNTA('WHO-COVID-19-candle'[Close]),
                DATESINPERIOD(
                    'WHO-COVID-19-candle'[date],
                    LASTDATE('WHO-COVID-19-candle'[date]),
                    interval,
                    DAY
                )
            )
        )
    )

　月単位の移動平均線といいながら、実際は月4週で計算しています。1カ月が28日、3カ月が84日、6カ月が168日です。intervalの値を変えて3つ作れば、移動平均線が完成です。Trend Linesの項目に入れ込みます。書式で、線の色を変更できます。

　でも、世界のグラフじゃなくて、日本のが見たいって思いますよね。M式の中で国名をフィルタしちゃえばいいのです。そのやり方は、宿題です。回答は、githubの方に入れてます。

　また、このデータを毎日自動更新して見れるようにしたいと思いますよね。Power BIのライセンスを持っていれば、app.powerbi.comにアップロードしてブラウザで閲覧できるようにできます。更新をスケジューリングすることもできます。また、共有でWebに公開すれば、「Power BI covid19レポート」のように公開することもできます。

　いかがでしたでしょうか。Power BIの日付を週で階層にすることができないため、下手な細工をすることになってしまいました。1つのテーブルにすべて入れ込むのは、いい方法ではありません。今回Mで行った作業も、DAXで記述することができるように思います。データスキーマの学習をし、ディメンションテーブルとファクトテーブルの使い方も理解しなければなりません。そうすれば、DAXがどのように役に立つのか理解し、使いこなすことができると思います。

今回わかったこと

1. Mは依存関係の順序で実行される
2. 変数の値は書き換えできない
3. 見やすくするには、手作業が必要
4. Ｗebに公開すれば、みんなで使える

そして、#4でわかること。
それはまだ・・・・・混沌の中。

作成したpbixファイルは、githubに登録してあります。