PowerQuery: JSON から必要なところだけを取り出して再構築

背景

Rest API 用のカスタムコネクターを作ろうとした際のメモ

やりたいことは

• Rest API の結果をそのまま返すと不要なデータが多いので、
  • 必要なとこだけ抽出
  • 名称を取得結果用に修正

これを使って、Teams のリアクション分析をしたい、という要望に応えられればな、と
MSさんがそのうち対応して不要になるんだろうけど

概要

1. API の取得結果と課題
2. 普通に取得した場合
3. JSON データの再構築
4. List から、Table へ

API の取得結果と課題

Teams の Messages の取得API とその例

課題

1. 一部の結果だけでよい。（上図で明るくしたとこだけ）
2. 以下のように、user/user になってたりと、展開すると見辛いので名称変更したい。
   
   

通常の取得と、今回の取得結果比較

通常

取得例

jsonDocument = Json.Document(Web.Contents("https://graph.microsoft.com/beta/teams/{teamId}/channels/{channelId}/messages")),
    result = Table.FromRecords( jsonDocument[value])

今回の結果

必要なところだけになっているので、イイネの集計には便利になるんじゃないかなぁと。

onBehalfOf と postUser はマージするか、onBehalfOf 無くしてもいいんじゃないかな、とは思うけど

JSON データの再構築

以下な感じで、必要なデータを取得しつつ、構造も短縮し名称も変更する

構造変換

jsonLists = List.Transform(currentJson[value], each [
    onBehalfOf = _[onBehalfOf][user],
    postUser = _[from][user],
    reactions = List.Transform( _[reactions], each [
        reactionType = _[reactionType],
        createdDateTime = _[createdDateTime],
        user = _[user][user]
    ]),
    link = _[webUrl]
]),

• List.Transform を使う事で、構造変化。
• 今回は、List->List があるので、それを List.Transform を多重に使って、user->user を短縮。

List, Record の構造を生成するときの参考は、以下の例が素敵

内容を単純にする為、null 確認を省いてるので、実際に使う場合は注意

List から、Table へ

単純に、先の再構築したテーブル構造をそのまま定義して渡して変換

テーブル化

jsonTable = Table.FromList(
    jsonLists,
    Record.FieldValues,
    type table [
        onBehalfOf = nullable [
                id = text,
                displayName = nullable text,
                userIdentityType = text
            ],
        postUser = nullable [
                id = text,
                displayName = nullable text,
                userIdentityType = text
            ],
        reactions= nullable {[
            reactionType = text,
            createdDateTime = datetime,
            user = nullable [
                id = text,
                displayName = nullable text,
                userIdentityType = text
            ]
        ]},
        link = text
    ]
)

あとがき

ここまで来たら、あとは、やりたかったカスタムコネクターを作るだけ！
週末に作って、来週には社内展開出来たらいいなぁ