Mから始めよう #2 ～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)

# ロウソク足チャートとは

前回、簡単な折れ線グラフを作成いたしました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d7e3fe9b-89d1-6a32-c4a9-3732fac482c6.png)
このように、曜日によってデータが上下がパターン化されているデータは、一定期間の平均をとり、移動平均線で表示するのがいいのですが、もう少しすっきりと表示させるには、株価チャートで用いられるロウソク足のチャートがあります。
以下のようなやつです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/96fcba87-c2fd-3821-a609-a0c7401928f7.png)
(Yahoo!ファイナンスより）

　ロウソク足チャートは、変化の激しい相場の流れを読むために、江戸時代の日本の天才相場師、本間宗久元が発明したといわれています。
　1本のろうそく足は、4つの値でできています。

始値：単位期間の開始時の値段
高値：期間中最も高かった値段
安値：期間中最も安かった値段
終値：単位期間の終了時の値段

　このうち、始値（はじめね）と終値（おわりね）で太い部分をつくり、上下の細い線は高値（たかね）、安値（やすね）を示します。始値と比べて終値が高い場合と安い場合では、太い部分の塗り方を変えます。上のグラフでは、終値が高い場合は中を塗らない、下がった場合は塗りつぶすとしていますが、別のものでは、上がる時は青く塗りって、下がるときは赤く塗るなど、やりかたはそれぞれです。

# データの加工
　前回作成したものを使用し、ホームの「データの変換」から「データの変換」を選択すると、クエリエディタが起動されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/68abad7e-bdad-38b5-1041-89ae11087fc4.png)

左側に前回作成したM式のクエリが表示されていますので、右クリックして複製し、名前を適当なものに変更します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3380aa12-9851-c947-2587-65436f7f5f49.png)

　画面右の適用されたステップで「名前が変更された列」が選択されているのを確認して、1本のろうそく足は1週間で、日曜日始まり、土曜日終わりで作っていいきます。
　まず、「ホーム」メニューのリボンから「グループ化」を選択し、日付毎に新規感染者数を合計します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/85122687-f30f-4508-c0e0-264c2f791382.png)
　次に、カスタム列「WeekOfYear」を追加し、Date_reportedを使って、単位となる週の値を作ります。

```:WeekOfYear
Date.Year([Date_reported]) * 100 + Date.WeekOfYear([Date_reported])
```

日曜日から始まり土曜日で終わる数値が入るカスタム列「DayOfWeek」を作ります。

```:DayOfWeek
Date.DayOfWeek([Date_reported])
```
メニューの「列の追加」から「条件列」を選択して列を追加します。
DayOfWeekは、0から6の値をとるので、0の時にOpen、6の時にCloseの列に新規感染者数が入り、それ以外の場合は0の値が入るようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/28cf73b1-b094-897a-e38d-bac707ba23bb.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/05353630-6401-8d53-eb6f-276acc424060.png)
「ホーム」メニューを選択し、グループ化の詳細設定を使ってWeekOfYearでグループ化します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/08f55245-a3a0-95ed-624c-341a53295190.png)
これで、元データが出来上がりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b558b5e0-16bb-3623-2385-6e9cd472dbac.png)
左上の閉じて適用ボタンを押して、レポート画面に戻ります。

# ビジュアルの追加
　ロウソク足チャートは、標準のビジュアルには含まれていないので、追加します。視覚化のアイコンの最後に「・・・」という部分をクリックし、AppSoureからcandleを検索し「Candlestick by OKViz」を追加します。(Microsoft AppSourceにログインする必要あり）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/361c3afb-6a90-79bd-e4ff-55f3c5f2ebef.png)
　キャンバス下にあるタブで、ページを追加し、2ページ目にロウソク足チャートの枠をつくり、WeekOfYear, Open, Close, High, Lowと順番に選択します。
グラフの右上あたりにマウスを持っていくと、その他オプションが表示されるので、「・・・」を選び、並び替え条件の「WeekOfYear」を選択して昇順で並び替えます。これで、ひとまず完成です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f48a5d93-ce0a-cac2-b0de-710a44b0cd02.png)

# うまく表示されない
　このグラフを日曜日に作ったなら、問題はありません。しかし、それ以外の曜日に作ると、次の土曜日のデータがないため、下のグラフのように終値が0になってしまいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f1a4f0e1-5771-5626-f2bd-b95f38f144f2.png)
　日曜限定で使用するグラフなら問題ないのですが、毎日更新したいなら、これじゃ都合が悪いのです。
　問題点は３つあって、１つは最後の終値が取れない問題と、もう１つは最初の始値がとれない問題があります。この解決方法として、最初の始値は、データの一番最初の日の値を使う、最後の終値は、最終取得日のデータを使うことで解決しましょう。3つ目は、X軸の値が日付でないことです。人に見せるなら、日付で表示させたいし、このままだと移動平均線の計算ができません。
　で、解決方法ですが、当然DAXではなくMでやります。

# 今回わかったこと

**1. ロウソク足チャートは日本人の発明**
**2. 計算結果を新しい列に追加できる**
**3. グループ化で合計、最大、最小 etc. がとれる**
**4. 標準にないビジュアルも追加できる**

## 続く
- [Mから始めよう #3 ～Power BIでM式を使う～ ](https://qiita.com/spumoni/private/9b89555dfd8b550c177f)

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