CData さんのブログを見て、「arXivのAPI実行してPower BIで分析できないかな」と思った際に得られた知見のメモです。件のブログは以下。

arXivのAPI に限らず、一覧取得するようなAPIを使う場合、一度に取得できる件数が限られていて次のまとまりを取得するためにページを指定するものがよくあります。単純なURLからデータを取得するのであれば「データを取得」 > 「Webから」でよいのですが、ページングが発生する場合にはそのままだと全件取得ができません。(1ページあたりの最大件数までしか取れない)

f:id:mofumofu_dance:20201029163235p:plain

ではどうするか。ページングの実装は様々ですが、今回はarXiv と Qiita を例にしてページングされた一覧の全件取得を行います。

arXiv APIではクエリパラメータに startとmax_results をセットすることでページングを提供しています。

http://export.arxiv.org/api/query?search_query=all:electron&start=0&max_results=10

また Qiitaに関してはクエリパラメータに pageと per_pageをセットします。

https://qiita.com/api/v2/items?query=tag%3APowerAutomate&per_page=20&page=2

いずれも start や page に対応する値をリストで持っていればURLを構成することは簡単にできます。

f:id:mofumofu_dance:20201029164750p:plain

問題はそこから先、このURLをもとにどうやってリクエストをして一覧を取得するかです。

Qiitaの場合 (JSON)

QiitaはレスポンスがJSONなので、以下のような式でリクエストURLからデータ取得を行います。

= Table.AddColumn(追加されたカスタム, "ResponseData", each Json.Document(Web.Contents([RequestURL], [Headers=[Authorization="Bearer ここにAPI Key"]])))

この結果、追加された各行は List 形式になります。ここまでくれば、あとはいつも通り列の右上のマークをクリックして展開を繰り返していくだけです。

f:id:mofumofu_dance:20201029165801p:plain

最終的には以下のようなクエリになりました。

let
    ソース = Table.FromList(List.Numbers(1,20), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"名前が変更された列 " = Table.RenameColumns(ソース,{{"Column1", "PageNumber"}}),
    変更された型 = Table.TransformColumnTypes(#"名前が変更された列 ",{{"PageNumber", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "RequestURL", each Text.Combine({"https://qiita.com/api/v2/items?query=tag%3APowerAutomate&per_page=20&page=",[PageNumber]})),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "ResponseData", each Json.Document(Web.Contents([RequestURL], [Headers=[Authorization="Bearer ここにAPI Key"]]))),
    #"展開された ResponseData" = Table.ExpandListColumn(追加されたカスタム1, "ResponseData"),
    #"展開された ResponseData1" = Table.ExpandRecordColumn(#"展開された ResponseData", "ResponseData", {"rendered_body", "body", "coediting", "comments_count", "created_at", "group", "id", "likes_count", "private", "reactions_count", "tags", "title", "updated_at", "url", "user", "page_views_count"}, {"ResponseData.rendered_body", "ResponseData.body", "ResponseData.coediting", "ResponseData.comments_count", "ResponseData.created_at", "ResponseData.group", "ResponseData.id", "ResponseData.likes_count", "ResponseData.private", "ResponseData.reactions_count", "ResponseData.tags", "ResponseData.title", "ResponseData.updated_at", "ResponseData.url", "ResponseData.user", "ResponseData.page_views_count"}),
    #"展開された ResponseData.user" = Table.ExpandRecordColumn(#"展開された ResponseData1", "ResponseData.user", {"name"}, {"ResponseData.user.name"}),
    変更された型1 = Table.TransformColumnTypes(#"展開された ResponseData.user",{{"ResponseData.created_at", type datetimezone}})
in
    変更された型1

arXivの場合 (XML)

arXivでは、レスポンスはXMLで返されます。先ほどと同様に、数値列からリクエストURLを作成します。

f:id:mofumofu_dance:20201029170520p:plain

レスポンスがXMLのテーブルの場合には、JSON.Documentの代わりに Xml.Tablesを使います。

= Table.AddColumn(追加されたカスタム2, "カスタム2", each Xml.Tables(Web.Contents([カスタム])))

XMLテーブルの場合には追加した列のデータ型はTableになります。この場合も同様に順次展開して行って、目的のデータをリストに追加していきましょう。

f:id:mofumofu_dance:20201029170833p:plain

最終的なクエリは以下の通りです。（この場合はelectronという文字列で1ページあたり1000件で取得している)

let
    ソース = Table.FromList(List.Numbers(1,10), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    追加されたカスタム = Table.AddColumn(ソース, "From", each (1000*([Column1]-1))),
    変更された型 = Table.TransformColumnTypes(追加されたカスタム,{{"From", type text}}),
    追加されたカスタム2 = Table.AddColumn(変更された型, "カスタム", each Text.Combine({"http://export.arxiv.org/api/query?search_query=all:electron&start=",[From],"&max_results=1000"})),
    カスタム1 = Table.AddColumn(追加されたカスタム2, "カスタム2", each Xml.Tables(Web.Contents([カスタム]))),
    #"展開された カスタム2" = Table.ExpandTableColumn(カスタム1, "カスタム2", {"entry"}, {"カスタム2.entry"}),
    #"展開された カスタム2.entry" = Table.ExpandTableColumn(#"展開された カスタム2", "カスタム2.entry", {"id", "updated", "published", "title", "summary", "author", "http://arxiv.org/schemas/atom", "link", "category"}, {"カスタム2.entry.id", "カスタム2.entry.updated", "カスタム2.entry.published", "カスタム2.entry.title", "カスタム2.entry.summary", "カスタム2.entry.author", "カスタム2.entry.http://arxiv.org/schemas/atom", "カスタム2.entry.link", "カスタム2.entry.category"}),
    #"展開された カスタム2.entry.author" = Table.ExpandTableColumn(#"展開された カスタム2.entry", "カスタム2.entry.author", {"name", "http://arxiv.org/schemas/atom"}, {"name", "http://arxiv.org/schemas/atom"}),
    #"展開された カスタム2.entry.category" = Table.ExpandTableColumn(#"展開された カスタム2.entry.author", "カスタム2.entry.category", {"Attribute:term", "Attribute:scheme"}, {"Attribute:term", "Attribute:scheme"}),
    削除された列 = Table.RemoveColumns(#"展開された カスタム2.entry.category",{"Column1", "From", "カスタム"}),
    変更された型1 = Table.TransformColumnTypes(削除された列,{{"カスタム2.entry.updated", type datetimezone}}),
    並べ替えられた行 = Table.Sort(変更された型1,{{"カスタム2.entry.updated", Order.Ascending}})
in
    並べ替えられた行