Power BI Desktopでパラメーターを使い人流データを可視化（1）

##プルダウンリストで選択

Power BI Desktopのパラメーター機能を使ってみることにました。

上記サイトで公開しされている**「人流」**のオープンデータの取得方式

例
https://opendata.corona.go.jp/api/ReductionRate?date=20200515&dataName=栃木県宇都宮駅周辺

を利用し、「日付」と「エリア」を選択して当該データをプルダウンメニューで取得できるようにしました。
以下のように操作できます。

例
https://opendata.corona.go.jp/api/ReductionRate?date=20200515&dataName=栃木県宇都宮駅周辺

日付については「YYYYMMDD」形式を数字型で変更できるようにしています。
この理由については次回で説明します。
今回は日付形式で選択できるようにしています。

##「パラメーター」を使用準備

Power BI Desktop**「パラメーター」**機能は、例えばデータの取得先などを変数とし、その変数を変更することで、取得範囲を変更できる機能です。
以下を参考にさせて頂きしました。

使ったデータソースは、内閣官房省の「新型コロナウイルス感染症対策サイト」で見つけたオープンデータです。

以下のように日付とエリアを指定して、「人の流れの変動推移」（エリアの中の人口の増減）のデータを取り込むことができます。

例
https://opendata.corona.go.jp/api/ReductionRate?date=20200515&dataName=栃木県宇都宮駅周辺

取得結果
{"errorInfo":{"errorFlag":"0","errorCode":null,"errorMessage":null},"itemList":[{"date":"2020/05/15","dataType":"12","dataName":"栃木県宇都宮駅周辺","comparisonPreDay":"-3.3","comparisonPreDeclare":"-21.0","comparisonPreSpread":"-44.2"}]}

まず、日付とエリアを指定して取り込みます。
Power BI Desktopでは、操作内容をスクリプト（M言語と呼ぶそうです）として記録します。
そのスクリプトを変更することで、今回パラメーターを使えるようにします。

使用した取り込みURL
https://opendata.corona.go.jp/api/ReductionRate?date=20210401&dataName=東京都新宿駅周辺

{"errorInfo":{"errorFlag":"0","errorCode":null,"errorMessage":null},"itemList":[{"date":"2021/04/01","dataType":"12","dataName":"東京都新宿駅周辺","comparisonPreDay":"-5.1","comparisonPreDeclare":"33.5","comparisonPreSpread":"-9.1"}]}

Power BI Desktopに取り込みます。

**「詳細エディター」**でスクリプトを確認します。

コピペできます。

let
    ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate?date=20210401&dataName=東京都新宿駅周辺")),
    テーブルに変換済み = Table.FromRecords({ソース}),
    削除された列 = Table.RemoveColumns(テーブルに変換済み,{"errorInfo"}),
    #"展開された itemList" = Table.ExpandListColumn(削除された列, "itemList"),
    #"展開された itemList1" = Table.ExpandRecordColumn(#"展開された itemList", "itemList", {"date", "dataType", "dataName", "comparisonPreDay", "comparisonPreDeclare", "comparisonPreSpread"}, {"itemList.date", "itemList.dataType", "itemList.dataName", "itemList.comparisonPreDay", "itemList.comparisonPreDeclare", "itemList.comparisonPreSpread"}),
    変更された型 = Table.TransformColumnTypes(#"展開された itemList1",{{"itemList.dataType", Int64.Type}, {"itemList.comparisonPreDay", type number}, {"itemList.comparisonPreDeclare", type number}})
in
    変更された型

スクリプトは後ほど加工します。

##リストを作成

一旦離れて、「パラメーター」を利用するために、変素で選択できる値のリストを作ります。
まず、エリア（地域）リストです。
先ほど取り込み使ったURLから**「&dataName=東京都新宿駅周辺」を外して**取得します。

https://opendata.corona.go.jp/api/ReductionRate?date=20210401

使うのはエリア列だけなので、その列だけ残します。

リスト制作の最後に、「リスト」に変換し、クエリ名を**「para_area」**に変更します。

**「パラメーター」**を設定します。

クエリ名を**「select_para_area」、種類を「テキスト」（「すべて」でも大丈夫のようです）、変更された値に「クエリ」（と設定することで他のクエリから選択できます）、そしてクエリに先ほど用意した「para_area」を選びます。
そして、現在の値にダミーとして、para_areaの先頭の値「北海道札幌駅周辺」**を指定します。
この値は、後でスクリプトを変更する際の“目印”となります。

パラメーターが設定されます。

「複製」をして、加工するクエリー**「パラメーター利用1」**を用意します。

##スクリプトにパラメーターを記述

「詳細エディター」で開いたスクリプトをテキストエディタにコピペします。

let
    ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate?date=20210401&dataName=東京都新宿駅周辺")),
    テーブルに変換済み = Table.FromRecords({ソース}),
    削除された列 = Table.RemoveColumns(テーブルに変換済み,{"errorInfo"}),
    #"展開された itemList" = Table.ExpandListColumn(削除された列, "itemList"),
    #"展開された itemList1" = Table.ExpandRecordColumn(#"展開された itemList", "itemList", {"date", "dataType", "dataName", "comparisonPreDay", "comparisonPreDeclare", "comparisonPreSpread"}, {"itemList.date", "itemList.dataType", "itemList.dataName", "itemList.comparisonPreDay", "itemList.comparisonPreDeclare", "itemList.comparisonPreSpread"}),
    変更された型 = Table.TransformColumnTypes(#"展開された itemList1",{{"itemList.dataType", Int64.Type}, {"itemList.comparisonPreDay", type number}, {"itemList.comparisonPreDeclare", type number}})
in
    変更された型

下記のように、エリアを指定する個所を先ほど設定したパラメーター**「select_para_area」**に書き換えます。

ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate?date=20210401&dataName=東京都新宿駅周辺")),
　↓　↓　↓
ソース = ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate" & "?date=20210402" & "&dataName=" & select_para_area)),

ところが、以下のようなメッセージが出てしまいました。

Formula.Firewall: クエリ 'パラメーター利用1' (ステップ '変更された型') は他のクエリまたはステップを参照しているため、データ ソースに直接アクセスできません。このデータの組み合わせを再構築してください。

調べたところ、以下のサイトを参考に**プライバシーレベルを「無視」**する設定にしました。
**「ファイル」→「オプション」**で設定変更可能です。

Web経由で取得したデータと他のデータを組み合わせる場合に引っかかります。
セキュリティ対策ための設定のようです。
「無視」してよいのか気になりましたが、今回はオープンデータのルールに沿ってデータを取得するので、動作優先で進めました。
なお、他のケースで「Web経由でパラメーターと組み合わせデータを取得」の際、プライバシーレベルを変更せずにうまく動作している例もあるので、回避策はあるように思えます。
後日調べることにし、先に進みます。

**「プレビューの更新」**を実行し、データ取得ができたことを確認できました。

次に、日付のリストを用意します。
URL末尾についているパラメータをすべて外して

https://opendata.corona.go.jp/api/ReductionRate

必要なデータを取得することできますが、データ量が多くなるので、エリアのパラメータだけ残し、

https://opendata.corona.go.jp/api/ReductionRate?dataName=東京都新宿駅周辺

Power BI Desktopに取り込みました。

「リスト」に使う日付列だけ尾を残します。

一応**「重複の削除」**をします。

先ほどのエリア同様に「リスト」に変換し、クエリ名は**「para_date」**としました。
なお、「/」を外すために、リストに変換する前に、列を「文字列」にし「/」→「」（ブランク）に変換しています。

日付を選択するパラメーター**「select_para_date」**を用意します。

詳細エディターで以下の記述を「select_para_date」に置き変えます。

ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate" & "?date=20210402" & "&dataName=" & select_para_area)),
　↓　↓　↓
ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate" & "?date=" & selerct_para_date & "&dataName=" & select_para_area)),

ところが、以下の黄色いエリアのメッセージが出たため、その指示に沿って表示された設定画面で、ブランクのエリアに「select_para_date」を設定します。

今度は以下のメッセージが出ました。
よくわからないのですが、select_para_dateの「YYYYMMDD」の型が問題となっているようです。
文字列として扱ってくれれば処理できると思ったのですが、「日付型」として扱っているのでしょうか。

このままで演算子「&」が働かないので、色々試した結果、**「select_para_date」の形式を「YYYYMMDD」から「?date=YYYYMMMMD」**としました。

ソース = Json.Document(Web.Contents("https://opendata.corona.go.jp/api/ReductionRate" & "?date=20210402" & "&dataName=" & select_para_area)),
　↓　↓　↓
ソース = Json.Document(Web.Contents(Text.From("https://opendata.corona.go.jp/api/ReductionRate" & selerct_para_date & "&dataName=" & select_para_area))),

とすることで解決しました。
「?date=」を付与することで、文字列型であることを明確にできたから、かもしれません。

なお、「YYYYMMDD」を「YYYYMMDDのDATE型」にし、それを「テキスト型変換」とスクリプト内で設定してもエラーは回避できました。
以下のような感じです。
ただ、この場合例えば、「20040401」を「44287」と数字型に置き換えたリストを用意することになります。

    ソース = Json.Document(Web.Contents(Text.From("https://opendata.corona.go.jp/api/ReductionRate" & "?date=" & Date.ToText(Date.From(para_date2), "YYYYMMDD")))),

##パラメーターをプルダウンから変更

設定したパラメータを変更してみます。
**「パラメータの管理」→「パラメーターの編集」**を選択します。

「select_para_area」に「東京都新宿駅周辺」を、「select_para_date」に「?date=20210427」をそれぞれのプルダウンリストから選択します。

「プレビューの更新」をすると、「20210427」の「東京都新宿駅周辺」のデータが取得できました。

こままでは1日分のデータしか取得できないので、Dateパラメーターを一回設定して、1週間部（7日分）のデータを取得できるようにします。
それは回を分けてこちらで説明します。

了