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

この記事は、[Microsoft Power BI Advent Calendar 2020](https://qiita.com/advent-calendar/2020/powerbi)の14日目の記事となります。

前回の記事はこちら。

- [Mから始めよう #1 ～Power BIでM式を使う～](https://qiita.com/spumoni/private/be353e618789f3ff0563)
- [Mから始めよう #2 ～Power BIでM式を使う～](https://qiita.com/spumoni/private/a35deddd679dea0eaab0)

# Mとは
 Power BIでMと言われているのは、Power Query Formula Languageが正式な名称です。M式、M数式、M言語、様々な呼び方をされています。由来をきちんと書かれたものが見つからないのですが、私はデータをマッシュアップ（mash-up)するので、Mと呼ばれていると解釈してます。
　通常、letで始まり、inで値を返す関数の形で使われます。GUIの操作で行う以上のことが、詳細エディターを開いて直接式を書くことで可能になります。詳細は、[Microsoft Docs](https://docs.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference)に書かれていますが、日付関数のことを「データ関数」と書かれている箇所もあったり（dateとdataの混同？)、結合関数を「コンバイナ関数」と書かれていたり、誤訳や特殊な用語に訳されたりして全体的に読みにくいです。英語と比べながら読むといいかもしれません。ページの右上に、「英語で読む」ボタンで切り替えられます。
　M式について、さらに詳しく知りたい場合は、[Ben Gribaudoのblog](https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let)に、わかりやすく書かれています。英語ですが、サンプルが多いので、自動翻訳と合わせても読みやすいです。
　少し動かしてみましょう、Power BIを起動し、データの取得から「空のクエリ」を選択し、「詳細エディッター」を開きます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/559bc174-c7e5-8712-7c94-cf0c066a4914.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/bc704626-5403-c6b4-c086-92404d108b38.png)
詳細エディターの中を全部消し、以下のようなコードを入力してみてください。

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

　式が続く場合に末尾に「,」がついていることに注意してください。最後に、inの後に続く値が返されます。完了ボタンを押すと、inに後に書かれたzの値が返されます。この結果は、5となります。この時、左側の適用したステップが以下のようになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9c9a1b38-6859-cce5-be78-8ff926451390.png)
　マウスで、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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d0fa6bb5-6378-1e06-ef5b-13fb62a7dc73.png)

```:レコード
let
    record1 = 
        [ 
            A = 1,  
            B = 2,  
            C = 3 
        ]
    
in
    record1
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0c91f36f-a183-8b10-5873-9627d208e39c.png)

```:テーブル
let
    table1 = 
        #table(
            {"a", "b", "c"}, 
            {
                {1, 2, 3},
                {4, 5, 6},
                {7, 8, 9}
            }
        )
in
    table1
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/dff3d350-61f3-9cfc-2980-7cb3983b935e.png)

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

# M関数の種類
 M関数は、[Microsoft Docs](https://docs.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference)では、以下の通り分類されています。

|カテゴリ|説明|
|:------|:--------|
|データへのアクセス関数|データにアクセスし、テーブルの値を返します|
|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の列を作成する処理を最後に書いてます。これは、グループ化の時にうまく残せば、わざわざ計算する必要はないのですが。

　これで、以下のようなグラフが出来上がりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/12c2e2f6-00c9-dcd3-df0a-1e27f4701bcf.png)
　移動平均線はまだ作ってませんでしたね。1カ月と3カ月、6カ月の3本の移動平均線を作成します。これは、Mでやるとあまりに手間がかかるので、さっくりDAXで書いてしまいましょう。以下のようにメジャーを3つ作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0dcc8a05-d359-693c-bbd6-5d131c8a0c41.png)
　メジャーの式は、[tentomushiさんのPower BI -移動平均(Moving Average)の計算／レポートの設定方法](https://note.com/tentomusea/n/n36c2b9057f56)をさくっと流用させていただきました。


```: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の項目に入れ込みます。書式で、線の色を変更できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/cce57324-bdf8-2e46-d156-3104d2e98d40.png)

　でも、世界のグラフじゃなくて、日本のが見たいって思いますよね。M式の中で国名をフィルタしちゃえばいいのです。そのやり方は、宿題です。回答は、[github](https://github.com/fukuyori/powerbi-advent-2020)の方に入れてます。

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

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

# 今回わかったこと

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

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

作成したpbixファイルは、[github](https://github.com/fukuyori/powerbi-advent-2020)に登録してあります。