More than 3 years have passed since last update.

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

Last updated at 2020-04-21Posted at 2020-04-15

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

前回の記事では Power BIデスクトップの準備と、Githubからジョンズ・ホプキンズ大の集計データをデータソースとするPower BIクエリを加える段階まで解説しました。

ここではPower BIクエリにさらに処理を加えてデータモデルを作りはじめます。データモデルの出来不出来で可視化したときの分かりやすさが変わってきます。良いデータモデルはデータから「意味」を取り出せる状態になっている、と言っていいんじゃないでしょうか（初心者談）。

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

この記事のゴールは、Confirmed (感染者数）、Deaths（死亡者数）、Recovered（回復数）の３つのデータを合わせた１つのMasterテーブルを作成するところまでです。

大まかな手順

Confirmedクエリの処理
Deathsクエリの処理
Recoveredクエリの処理
３つのクエリをMasterテーブルとして繋ぐ

ではさっそくクエリの編集から始めましょう。

Power Query エディタでクエリを加工する

データソースの素のデータは、例えるなら「宝石の原石」のようなもので、そのままでは価値がある情報を簡単には得られません。そこで、それらのデータをさらに処理して（transform 変身させて）、整理します。

これらの作業はPower Query エディタで行います。下図のメニューからエディタを開きましょう。
（このメニューは何度も何度もクリックすることになります！）

扱いやすいようにクエリの名前を短くする

Power Query エディタが開いたら、３つのクエリが見えます。ただ、名前が長いので、とりあえず名前を短くしましょう。

rename メニューを使います。

それぞれを

Confirmed
Deaths
Recovered

に変更した結果：

さて、これで３つのクエリとそのデータの追加処理の準備が出来ました。でもその前に、Power Query エディタの２つの便利機能をオンにしておきましょう。

Power Query エディタの便利機能の設定

View タブの以下の２つの機能をオンにします。

Formula Bar - UIの背後で設定されているクエリやDAX式（後述）が見えるようになります。ここに直接入力する方が楽な場合があるので常に表示されている方が便利
Column quality - 各カラムの値の状態（Validかエラー状態か、など）を表示してくれます。

クエリのデータをさらに処理しよう

クエリのデータを加工処理する工程は、細かく解説していくと長くなってしまうので、今回はすべての処理の手順を表すクエリを一括コピペすることにします。料理番組の「こちらにすでに・・・」のパターンですね。（別の記事でそれそれの処理について解説するかもしれません）

データソースのスキーマはほとんど同じですが、少しことなる処理をするので、１つずつ進めていきます。やり方はクエリのアドバンスドエディタ(Advanced editor)を使って、一括コピペで行います。

`Confirmed` クエリの処理

Confirmed クエリが選択されている状態で、ViewタブからAdvanced editor を選びます。

Advanced editor ダイアログが表示されたら、エディタ内のすべてを選択して、以下のクエリで上書きします。

let
    Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Province/State", type text}, {"Country/Region", type text}, {"Lat", type number}, {"Long", type number}, {"1/22/20", Int64.Type}, {"1/23/20", Int64.Type}, {"1/24/20", Int64.Type}, {"1/25/20", Int64.Type}, {"1/26/20", Int64.Type}, {"1/27/20", Int64.Type}, {"1/28/20", Int64.Type}, {"1/29/20", Int64.Type}, {"1/30/20", Int64.Type}, {"1/31/20", Int64.Type}, {"2/1/20", Int64.Type}, {"2/2/20", Int64.Type}, {"2/3/20", Int64.Type}, {"2/4/20", Int64.Type}, {"2/5/20", Int64.Type}, {"2/6/20", Int64.Type}, {"2/7/20", Int64.Type}, {"2/8/20", Int64.Type}, {"2/9/20", Int64.Type}, {"2/10/20", Int64.Type}, {"2/11/20", Int64.Type}, {"2/12/20", Int64.Type}, {"2/13/20", Int64.Type}, {"2/14/20", Int64.Type}, {"2/15/20", Int64.Type}, {"2/16/20", Int64.Type}, {"2/17/20", Int64.Type}, {"2/18/20", Int64.Type}, {"2/19/20", Int64.Type}, {"2/20/20", Int64.Type}, {"2/21/20", Int64.Type}, {"2/22/20", Int64.Type}, {"2/23/20", Int64.Type}, {"2/24/20", Int64.Type}, {"2/25/20", Int64.Type}, {"2/26/20", Int64.Type}, {"2/27/20", Int64.Type}, {"2/28/20", Int64.Type}, {"2/29/20", Int64.Type}, {"3/1/20", Int64.Type}, {"3/2/20", Int64.Type}, {"3/3/20", Int64.Type}, {"3/4/20", Int64.Type}, {"3/5/20", Int64.Type}, {"3/6/20", Int64.Type}, {"3/7/20", Int64.Type}, {"3/8/20", Int64.Type}, {"3/9/20", Int64.Type}, {"3/10/20", Int64.Type}, {"3/11/20", Int64.Type}, {"3/12/20", Int64.Type}, {"3/13/20", Int64.Type}, {"3/14/20", Int64.Type}, {"3/15/20", Int64.Type}, {"3/16/20", Int64.Type}, {"3/17/20", Int64.Type}, {"3/18/20", Int64.Type}, {"3/19/20", Int64.Type}, {"3/20/20", Int64.Type}, {"3/21/20", Int64.Type}, {"3/22/20", Int64.Type}, {"3/23/20", Int64.Type}, {"3/24/20", Int64.Type}, {"3/25/20", Int64.Type}, {"3/26/20", Int64.Type}, {"3/27/20", Int64.Type}, {"3/28/20", Int64.Type}, {"3/29/20", Int64.Type}, {"3/30/20", Int64.Type}, {"3/31/20", Int64.Type}, {"4/1/20", Int64.Type}, {"4/2/20", Int64.Type}, {"4/3/20", Int64.Type}, {"4/4/20", Int64.Type}, {"4/5/20", Int64.Type}, {"4/6/20", Int64.Type}, {"4/7/20", Int64.Type}, {"4/8/20", Int64.Type}, {"4/9/20", Int64.Type}, {"4/10/20", Int64.Type}, {"4/11/20", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Province/State", "Country/Region", "Lat", "Long"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Status", each "Confirmed"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LocationKey", each if ([#"Province/State"] = "") 
     then [#"Country/Region"] & "-" & [#"Country/Region"]
     else [#"Country/Region"] & "-" & [#"Province/State"])
in
    #"Added Custom1"

コピペした結果：

この状態になったらDoneボタンでダイアログを閉じます。ダイアログから戻ってきたら、Power Query エディタでConfirmedクエリが以下のように変更されているはずです。

重要な点は

日ごとの値（この場合、Confirmed 感染確認数）がカラムとして加わっていること
Statusという新しいカラムが加えられ、すべて値がConfirmedになっていること

`Deaths` クエリの処理

同じ要領で以下のクエリでコピペ更新しましょう。

let
    Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Province/State", type text}, {"Country/Region", type text}, {"Lat", type number}, {"Long", type number}, {"1/22/20", Int64.Type}, {"1/23/20", Int64.Type}, {"1/24/20", Int64.Type}, {"1/25/20", Int64.Type}, {"1/26/20", Int64.Type}, {"1/27/20", Int64.Type}, {"1/28/20", Int64.Type}, {"1/29/20", Int64.Type}, {"1/30/20", Int64.Type}, {"1/31/20", Int64.Type}, {"2/1/20", Int64.Type}, {"2/2/20", Int64.Type}, {"2/3/20", Int64.Type}, {"2/4/20", Int64.Type}, {"2/5/20", Int64.Type}, {"2/6/20", Int64.Type}, {"2/7/20", Int64.Type}, {"2/8/20", Int64.Type}, {"2/9/20", Int64.Type}, {"2/10/20", Int64.Type}, {"2/11/20", Int64.Type}, {"2/12/20", Int64.Type}, {"2/13/20", Int64.Type}, {"2/14/20", Int64.Type}, {"2/15/20", Int64.Type}, {"2/16/20", Int64.Type}, {"2/17/20", Int64.Type}, {"2/18/20", Int64.Type}, {"2/19/20", Int64.Type}, {"2/20/20", Int64.Type}, {"2/21/20", Int64.Type}, {"2/22/20", Int64.Type}, {"2/23/20", Int64.Type}, {"2/24/20", Int64.Type}, {"2/25/20", Int64.Type}, {"2/26/20", Int64.Type}, {"2/27/20", Int64.Type}, {"2/28/20", Int64.Type}, {"2/29/20", Int64.Type}, {"3/1/20", Int64.Type}, {"3/2/20", Int64.Type}, {"3/3/20", Int64.Type}, {"3/4/20", Int64.Type}, {"3/5/20", Int64.Type}, {"3/6/20", Int64.Type}, {"3/7/20", Int64.Type}, {"3/8/20", Int64.Type}, {"3/9/20", Int64.Type}, {"3/10/20", Int64.Type}, {"3/11/20", Int64.Type}, {"3/12/20", Int64.Type}, {"3/13/20", Int64.Type}, {"3/14/20", Int64.Type}, {"3/15/20", Int64.Type}, {"3/16/20", Int64.Type}, {"3/17/20", Int64.Type}, {"3/18/20", Int64.Type}, {"3/19/20", Int64.Type}, {"3/20/20", Int64.Type}, {"3/21/20", Int64.Type}, {"3/22/20", Int64.Type}, {"3/23/20", Int64.Type}, {"3/24/20", Int64.Type}, {"3/25/20", Int64.Type}, {"3/26/20", Int64.Type}, {"3/27/20", Int64.Type}, {"3/28/20", Int64.Type}, {"3/29/20", Int64.Type}, {"3/30/20", Int64.Type}, {"3/31/20", Int64.Type}, {"4/1/20", Int64.Type}, {"4/2/20", Int64.Type}, {"4/3/20", Int64.Type}, {"4/4/20", Int64.Type}, {"4/5/20", Int64.Type}, {"4/6/20", Int64.Type}, {"4/7/20", Int64.Type}, {"4/8/20", Int64.Type}, {"4/9/20", Int64.Type}, {"4/10/20", Int64.Type}, {"4/11/20", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Province/State", "Country/Region", "Lat", "Long"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Status", each "Deaths"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LocationKey", each if ([#"Province/State"] = "") 
     then [#"Country/Region"] & "-" & [#"Country/Region"]
     else [#"Country/Region"] & "-" & [#"Province/State"])
in
    #"Added Custom1"

`Recovered` クエリの処理

最後のクエリも同じ手順で。

let
    Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Province/State", type text}, {"Country/Region", type text}, {"Lat", type number}, {"Long", type number}, {"1/22/20", Int64.Type}, {"1/23/20", Int64.Type}, {"1/24/20", Int64.Type}, {"1/25/20", Int64.Type}, {"1/26/20", Int64.Type}, {"1/27/20", Int64.Type}, {"1/28/20", Int64.Type}, {"1/29/20", Int64.Type}, {"1/30/20", Int64.Type}, {"1/31/20", Int64.Type}, {"2/1/20", Int64.Type}, {"2/2/20", Int64.Type}, {"2/3/20", Int64.Type}, {"2/4/20", Int64.Type}, {"2/5/20", Int64.Type}, {"2/6/20", Int64.Type}, {"2/7/20", Int64.Type}, {"2/8/20", Int64.Type}, {"2/9/20", Int64.Type}, {"2/10/20", Int64.Type}, {"2/11/20", Int64.Type}, {"2/12/20", Int64.Type}, {"2/13/20", Int64.Type}, {"2/14/20", Int64.Type}, {"2/15/20", Int64.Type}, {"2/16/20", Int64.Type}, {"2/17/20", Int64.Type}, {"2/18/20", Int64.Type}, {"2/19/20", Int64.Type}, {"2/20/20", Int64.Type}, {"2/21/20", Int64.Type}, {"2/22/20", Int64.Type}, {"2/23/20", Int64.Type}, {"2/24/20", Int64.Type}, {"2/25/20", Int64.Type}, {"2/26/20", Int64.Type}, {"2/27/20", Int64.Type}, {"2/28/20", Int64.Type}, {"2/29/20", Int64.Type}, {"3/1/20", Int64.Type}, {"3/2/20", Int64.Type}, {"3/3/20", Int64.Type}, {"3/4/20", Int64.Type}, {"3/5/20", Int64.Type}, {"3/6/20", Int64.Type}, {"3/7/20", Int64.Type}, {"3/8/20", Int64.Type}, {"3/9/20", Int64.Type}, {"3/10/20", Int64.Type}, {"3/11/20", Int64.Type}, {"3/12/20", Int64.Type}, {"3/13/20", Int64.Type}, {"3/14/20", Int64.Type}, {"3/15/20", Int64.Type}, {"3/16/20", Int64.Type}, {"3/17/20", Int64.Type}, {"3/18/20", Int64.Type}, {"3/19/20", Int64.Type}, {"3/20/20", Int64.Type}, {"3/21/20", Int64.Type}, {"3/22/20", Int64.Type}, {"3/23/20", Int64.Type}, {"3/24/20", Int64.Type}, {"3/25/20", Int64.Type}, {"3/26/20", Int64.Type}, {"3/27/20", Int64.Type}, {"3/28/20", Int64.Type}, {"3/29/20", Int64.Type}, {"3/30/20", Int64.Type}, {"3/31/20", Int64.Type}, {"4/1/20", Int64.Type}, {"4/2/20", Int64.Type}, {"4/3/20", Int64.Type}, {"4/4/20", Int64.Type}, {"4/5/20", Int64.Type}, {"4/6/20", Int64.Type}, {"4/7/20", Int64.Type}, {"4/8/20", Int64.Type}, {"4/9/20", Int64.Type}, {"4/10/20", Int64.Type}, {"4/11/20", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Province/State", "Country/Region", "Lat", "Long"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Status", each "Recovered"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "LocationKey", each if ([#"Province/State"] = "") 
     then [#"Country/Region"] & "-" & [#"Country/Region"]
     else [#"Country/Region"] & "-" & [#"Province/State"])
in
    #"Added Custom1"