ジョンズ・ホプキンズ大の新型コロナウイルスのデータをPower BIで可視化してみよう（3/5）

# 前回の記事（2/5）のまとめ

[前回の記事](https://qiita.com/yoshiwatanabe/items/861b12b77f43262612db)では、```Confirmed``` (感染者数）、```Deaths```（死亡者数）、```Recovered```（回復数）の３つのデータを合わせた１つの```Master```テーブルを作成するところまで完了しました。

# 今回の記事（3/5）のゴール

この記事ではさらに4つのテーブル（およびそれを作成するためのクエリ）を作成します

* 一番最近の日のデータのみを持つ```Latest``` テーブルを作る
* 場所を表す```Location``` テーブルを作る
* Confirmed、Deaths、Recoveredなどのステータスを表す```Status``` テーブルを作る
* メジャーの定義を一か所で整理できるように```_Measure```テーブルを作る（メジャーに関しては後述）
* 可視化した際に役に立つテーブルのリレーションシップを定義する

# 一番最近の日のデータのみを持つ```Latest``` テーブルを作る

ジョンズ・ホプキンズ大のデータは2020年の1月ぐらいからのデータですが、現時点の合計などは直近のデータ（例えば今日は4月14日なので4月13日のデータ）さえあれば得られます。そのために```Latest```と名付けたテーブルを作成します。

手順は簡単です

1. ```Master```クエリの複製を作り```Latest```という名前にする
2. 一番最近の日のデータ以外をフィルターで取り除く

まず複製を作り：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/2c430668-0c22-e776-98e3-1dcf38b85dca.png)

名前を変更した後、Advanced Editorを開いて以下のクエリをコピペします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/e7e366f9-aad0-1149-14f0-1fbf25b20c73.png)

```
let
    Source = Table.Combine({Confirmed, Deaths, Recovered}),
    #"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date]) in each [Date] = latest),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", Int64.Type}})
in
    #"Changed Type"
```

Dateカラムが一番最近の日にみの```Latest```テーブルを作成しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/3e65c270-2de9-030a-9f8c-9f8c7a02129b.png)

# 場所を表す```Location``` テーブルを作る

```Latest``` テーブルを作ったのと全く同じ手順で、```Location```という名前のテーブル（クエリ）を作ります。コピペするクエリの内容は以下です。

```
let
    Source = Table.Combine({Confirmed, Deaths, Recovered}),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Province/State", "Country/Region"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "LocationKey",
 each 
     if ([#"Province/State"] = "") 
     then [#"Country/Region"] & "-" & [#"Country/Region"]
     else [#"Country/Region"] & "-" & [#"Province/State"]
)
in
    #"Added Conditional Column"
```

下図のようになっていれば完了です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/e483b016-6871-62d4-3bd3-71a473656e69.png)

ここでは、```LocationKey```というカラムが加えられていることに着目してください。実は同様のカラムが```Master```テーブルと```Latest```テーブルでも定義されています。これで、テーブル間に関係を持たせることが出来ます（後述）。

# ステータスを表す```Status``` テーブルを作る

このテーブルは手動で定義します。ホームタブの ```Enter Data``` から```Create Table```ダイアログを開きます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/24ca4c28-615b-777d-9558-5d7f890034ec.png)

直接、値を入力して、テーブルの名前を指定したらOKボタンでダイアログを閉じます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/24fe62d3-976c-fe8d-66de-328517d81d4d.png)

以下のようになってるはずです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/6cb11cb7-852d-1057-3cdd-c9f52b1d6487.png)

この```Status```テーブルにはカラムがひとつだけありますが名前がデフォルトの```Column1```になっているので、ダブルクリックして名前を```Status```に変更しましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/55dde5a2-bdc6-85bf-996a-992aab9fdfb0.png)

テーブル名もカラム名も```Status```なので、それが気になるなら別の名前にしても構いません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/95b766b2-bb0a-b057-b568-d7af616649a1.png)

これで ```Status```テーブルの作成が完了しました。

# ```_Measure```テーブルを作る

後述しますがメジャーはPower BIのもっともパワフルな機能です。メジャー機能が無かったらPower BIという製品はほぼ無価値だと言ってもいいでしょう。

メジャーは、クエリやテーブルに対して横断的に定義できるので、対象になるクエリやテーブルではなく、独立したダミーのテーブルの一部として定義することにします。（対象のクエリの一部として定義しても全く問題ありませんし、むしろその方が整理しやすい場合もあるでしょう。）

ダミーなテーブルなので作成手順は超簡単です。```Status```テーブルを作成した要領で「空のテーブル」を作るだけです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/c421c119-0393-4dbb-61df-ea6c9f0b0764.png)

# Power Query エディタでの準備は完了

だいたいこのぐらいでPower Query エディタでやることは終わりです。もちろん、通常の開発工程では何度もPower Query エディタと、Power BIデスクトップの間を行ったり来たりすることになりますが、今回はクエリを全部準備していたので、ここでPower Query エディタを終了します。

Power Queryエディタを終了して、Power BIデスクトップに戻るには、ひとつの「儀式みたいな」過程を通過します。ホームの```Close & Apply```がそれです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/a9a611e2-53a6-13f7-7db4-e85447bf2cd1.png)

クエリを変更し、その変更内容をPower BIデスクトップで定義した図などに反映させるのが```Apply```です。それをせずにそっとPower Queryエディタを閉じたい時は```Close```のみを選びます。

変更が反映されるのにしばらく時間が係る場合もあるので、下図のようなダイアログが表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/40cc9386-cc05-36ea-579d-eb6a94c928fd.png)

反映された結果、作成したクエリが```Fields```タブに表示されればここまでの手順は完了です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/736de8d5-c9ee-92c1-c45f-014fa7434af8.png)

# テーブルのリレーションシップを定義する

この作業はPower BIデスクトップで行います。Power Queryエディタではないですから混乱の無きよう。

Power BIデスクトップには３つの作業画面があります。

* Report
* Data
* Model

の3つですが、テーブルの間にリレーションを定義するのは3つ目のModelで行います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/05bc5008-c8ce-08a6-b7ae-5e591bf60d2f.png)

いくつかのテーブルは右方向に配置されていて見えなくなっているかもしれません。スクロールしてマウスでドラッグして見えるところまで持ってきましょう。
今回必要なテーブルは

* Main
* Latest
* Status
* Location

の４つです。例として以下のような配置にしてみましょう。

すでにテーブル間にリレーション（矢印が付いた線。下図では黄色表示）が定義されている場合はいったん削除してしまいましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/5d92e5b5-b26f-6ac7-7615-d4dfa1636680.png)

次に、これら４つのテーブルに対してリレーションを定義していきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/e2ae4c73-b5f0-78c8-e121-d4623a43d492.png)

具体的には以下のテーブルの組み合わせにリレーションを定義します。

* ```Master```テーブルと```Status```テーブル
* ```Latest```と```Status```テーブル
* ```Master```テーブルと```Location```テーブル
* ```Latest```と```Location```テーブル

##  ```Master```テーブルと```Status```テーブルにリレーションを定義する

例として、この２つのテーブルに対してリレーションを定義する手順を解説します。

下図で示す番号に従います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/1eb45017-e2ac-4580-c22f-f78b28e9bb5a.png)

すると新規のリレーションシップが作成されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/ae6c9e13-ad20-0a0f-b4cf-8087c92d4329.png)

モデルにもリレーションシップを表す線分が現れます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/7c9d1a4a-923a-8dda-617d-350dc2faceef.png)

##　残り３つのリレーションを定義する

同じ要領で、残りの３つのリレーションを定義しましょう。

* ```Latest```と```Status```テーブル
* ```Master```テーブルと```Location```テーブル
* ```Latest```と```Location```テーブル

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/7051234c-18d3-edb2-986e-7ab8f559a05a.png)

Locationテーブルとのリレーションは```LocationKey```カラムで関連付けされることに注意。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/c251e40d-8963-c22c-e121-40e040e20c82.png)

４つすべてのリレーションを定義しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/8aed5274-c1d5-545e-3cdb-a2da67a69748.png)

モデルにもリレーションが表示されたら完了です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/103139/f2df2b46-70d2-8272-facd-145cefff10bc.png)

# 4/5に続く

[次の記事](https://qiita.com/yoshiwatanabe/items/9eb49cff576344d0c183)ではモデルをもとにメジャーを作成していきます。