ExcelのPowerQueryでJSONを処理する
背景
という記事に
とコメントしたのがきっかけで
ExcelのPowerQueryでは実現が難しいとの指摘がありました。
ということで、PowerQueryでは大変な手間がかかる例を実現したいと思います。
こちらの画像のような表がゴールとなります。
実装
PowerQueryの起動
Excelのメニューの「空のクエリ」から起動できます。
本来はGUIで操作できますが、今回はデータソースなどを準備していないため、「詳細エディター」に直接入力します。
PowerQueryの構文
PowerQueryはM式言語と呼ばれる独自のクエリ言語で記述します。
この言語には、リスト・レコード・テーブルといったデータ型や、各種関数が用意されています。今回は
を用いることでREST APIを呼び出し、結果として得られるJSONを解析します。
Googleのルート検索API
Googleでは各種APIが提供されていて、ルート検索APIとしてDirection APIが用意されています。
この中で、リクエストパラメーターやレスポンス仕様も説明されています。
送信すべきリクエスト
Direction APIとして送信すべきURLは
https://maps.googleapis.com/maps/api/directions/json
でパラメーターは
origin=相原駅destination=新宿駅transit=transitkey=APIキー
のようになるかと思います。
また参照すべき結果は
-
routesの - 配列の先頭の
-
legsの - 配列の先頭の
-
durationの value
の個所に所要時間（単位：秒）となります。
PowerQueryの関数化
まずはDirection APIのクエリパラメータを作成します。
(発駅, 着駅) =>
let
parameter = [origin=発駅, destination=着駅, transit="transit", key="APIキー"]
in
parameter
リクエストを投げます。
(発駅, 着駅) =>
let
parameter = [origin=発駅, destination=着駅, transit="transit", key="APIキー"],
response = Web.Contents("https://maps.googleapis.com/maps/api/directions/json", [Query=parameter])
in
response
JSONを解析して、参照すべき結果を取り出します。
(発駅, 着駅) =>
let
parameter = [origin=発駅, destination=着駅, transit="transit", key="APIキー"],
response = Web.Contents("https://maps.googleapis.com/maps/api/directions/json", [Query=parameter]),
seconds = Json.Document(response)[routes]{0}[legs]{0}[duration][value]
in
seconds
データ型をdurationにします。
(発駅, 着駅) =>
let
parameter = [origin=発駅, destination=着駅, transit="transit", key="APIキー"],
response = Web.Contents("https://maps.googleapis.com/maps/api/directions/json", [Query=parameter]),
seconds = Json.Document(response)[routes]{0}[legs]{0}[duration][value]
in
#duration(0, 0, 0, seconds)
クエリ式全体
発駅リストと着駅リストは必要であればテキストファイルなり外部データを参照すればよいのですが、本質的ではないので、直接書き込みます。
クエリ式全体としてはこのような形になります。
let
所要時間取得 = (発駅, 着駅) =>
let
parameter = [origin=発駅, destination=着駅, transit="transit", key="APIキー"],
response = Web.Contents("https://maps.googleapis.com/maps/api/directions/json", [Query=parameter]),
seconds = Json.Document(response)[routes]{0}[legs]{0}[duration][value]
in
#duration(0, 0, 0, seconds),
発駅リスト = {"相原駅", "青井駅", "青砥駅"},
着駅リスト = {"新宿駅", "渋谷駅", "品川駅"},
発着表 = Table.ExpandListColumn(Table.AddColumn(Table.FromList(発駅リスト, null, {"発駅"}), "着駅", each 着駅リスト), "着駅"),
所要時間 = Table.AddColumn(発着表, "所要時間", each 所要時間取得([発駅], [着駅])),
ピボット = Table.Pivot(所要時間, 着駅リスト, "着駅", "所要時間")
in
ピボット
結果確認
適用したステップから「発着表」を選択すると
総当たりの表が構築されていることが確認でき、続く「所要時間」では
時間列が追加されていることがわかります。最終的な「ピボット」はもちろん
行列分解されてマトリックスが所要時間で埋められていることが確認できます。
Excelへの取り込み
「閉じて読み込む」を押すと
無事、Excelに取り込まれました。
所要時間はもちろん時間情報として認識されているので、Excel上でも書式設定してやれば
だいたいの形になったでしょうか。
最後に
ExcelとしてはPowerQueryから結果を受け取っているだけですので、クエリの更新を行わない限りは再実行される心配はありません。
問題はAPIキーを埋め込んでしまっていることでしょうか。ここもやろうと思えば対策はありますが、またの機会に。
追記
Distance Matrix APIを使うと、さらに効率よくマトリックスで所要時間を得ることができます。
ただし、返されたJSONを解析して表形式にするのは、元記事でも指摘されているように割と面倒です。（あとAPIドキュメントが間違っている罠もありそうです。）
let
発駅リスト = {"相原駅", "青井駅", "青砥駅"},
着駅リスト = {"新宿駅", "渋谷駅", "品川駅"},
origins = Text.Combine(発駅リスト, "|"),
destinations = Text.Combine(着駅リスト, "|"),
Query = [origins=origins, destinations=destinations, mode="TRANSIT", language="ja-JP", key="APIキー"],
response = Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json", [Query=Query]),
json = Json.Document(response),
durations = List.Transform(json[rows], each List.Transform([elements], each #duration(0, 0, 0, [duration][value]))),
records = List.Transform(durations, each Record.FromList(_, 着駅リスト)),
rows = Table.FromRows(List.Zip({発駅リスト, records}), {"発駅", "着駅リスト"}),
table = Table.ExpandRecordColumn(rows, "着駅リスト", 着駅リスト)
in
table
Discussion