この記事は、Microsoft Power BI Advent Calendar 2020の14日目の記事となります。
前回の記事はこちら。
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式を確認すると、
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で作業して、詳細エディターに貼り付けました。
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)の計算／レポートの設定方法をさくっと流用させていただきました。
１カ月移動平均線 =
-- 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に登録してあります。