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

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

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

https://corona.go.jp/dashboard/

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/28fea166-7faf-d073-618b-0077268f89e1.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/676e36ac-708b-db9c-4d53-900827525892.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/8feff852-4958-eb0a-8524-684ceaa642e4.png)


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

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

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

https://note.com/tentomusea/n/n0ef8e24b80d8

https://note.com/tentomusea/n/n5f89e0c6c162

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

https://corona.go.jp/dashboard/

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

>例
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に取り込みます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/27b59e47-18f6-a1a1-7b1b-01349198c90e.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/735257db-6a3d-e807-73ce-94e7bfafc432.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/5a3fd92b-b795-c06d-5487-17062fc0f18e.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/3e353f15-aef0-544b-d6c8-1dff52e4f901.png)

コピペできます。

```javascript
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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ea5640b5-6b0b-2bee-afdf-915d9a2055f1.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ace70631-a9f0-8795-bb8c-cdff7349e525.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/ff1b8afd-ee2b-1edd-3d76-9fda2fea95e9.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/d6e85799-1051-bd7c-5fc6-abcbcf090b88.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/54f2eef4-af21-eb5a-d002-413e330f9ada.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/c581adde-34aa-ab1c-cb07-bde2945862ed.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/79b172d5-8fc1-38fc-671b-fc2ec3905130.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/073703b7-1880-c9e9-0398-1a98d7d58197.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/cf6753fc-fc07-4635-5ed2-1b35d5e317be.png)

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

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

```javascript
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' (ステップ '変更された型') は他のクエリまたはステップを参照しているため、データ ソースに直接アクセスできません。このデータの組み合わせを再構築してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/466c7495-a4cf-77f0-c9f7-eb9606c13230.png)

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

https://oggylife.com/archives/1853

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/3d4a54e4-1348-fbb7-a152-787c4bbd0367.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/e9960105-fd8a-b97c-056a-cbfe0e2ce391.png)

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

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

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/0da80433-f0cc-4609-9c22-5f01ff2d5758.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/de71d5f0-6e75-34fe-678f-b85c72a27cee.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/e4599294-3f0c-cac9-d9d8-ffff8aba71a7.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/27c8ca24-b3b9-4338-666b-e615e5294469.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/f7c77348-d100-0e51-57cd-ede63957dc95.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/4d042af1-7b9e-c39c-ae3e-2ffd8fa19603.png)

詳細エディターで以下の記述を「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」を設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/487ce0c8-542b-8f58-e4dc-7f42dd04b55d.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/2fe69010-3d06-2fc2-5aee-c59ca8c58462.png)

このままで演算子「&」が働かないので、色々試した結果、**「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」と数字型に置き換えたリストを用意することになります。

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/e4df3f84-96b1-d518-5835-51a4d13a1619.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1143963/706b8cca-937e-3efa-f1f0-91f45be6ee08.png)

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

こままでは1日分のデータしか取得できないので、Dateパラメーターを一回設定して、1週間部（7日分）のデータを取得できるようにします。
それは回を分けて[こちら](https://qiita.com/text_owleyes_0214/items/a55af2b46d5a78a42468)で説明します。

**了**