PowerQuery: @OData.nextlink を処理する関数。nextlink がある限り取得する version

背景

以下を作ったあとで、ID にこだわる必要なかったことに気付き、修正版を上奏

概要

@OData.nextlink を取得して、存在する限りひたすら取得する

大量に不要な場合に、件数上限を設定したほうがよかったかもしれない・・
その場合は、List.Generate() で Count 条件入れればいいから、まぁいいか

カスタム関数

Pagenation 関数 nextlink 版

(siteUrl as text, relativePath as text) => 
let
    retreiveList = (siteUrl as text, relativePath as text, query as record) => 
        let
            retreivedList = try Function.InvokeAfter(
                ()=> Xml.Tables(Web.Contents(
                    siteUrl,
                    [
                        RelativePath = relativePath,
                        Query=query
                    ]
                )),
                #duration(0,0,0,0.1)
            )
            otherwise null,
            entry = retreivedList{0}[entry],
            content = Table.ExpandTableColumn(entry, "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"metadata"}),
            metadata = Table.ExpandTableColumn(content, "metadata", {"properties"}, {"properties"}),
            properties = Table.ExpandTableColumn(metadata, "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"data"}),
            result = Table.SelectColumns(properties,{"data"}),
            nextLink = try retreivedList[link]{0}{0}[#"Attribute:href"] otherwise "",
            hasNext = if nextLink = "" then false else true
        in 
            [
                result = result,
                hasNext = hasNext,  // @OData.nextlink があるか？
                nextLink = nextLink   // @OData.nextlink の確認用
            ],
    retreivedPagenationList = List.Generate(
        () => [
            firstData = retreiveList(siteUrl, relativePath, [
                            // #"$skiptoken" = "Paged=TRUE&p_ID=0", Pagenationを強制する場合に設定
                            // #"$top" = "2"
                    ]),             
            data = firstData[result],            
            linkUri = if firstData[nextLink] = null then "" else Uri.Parts(firstData[nextLink]),
            baseUrl = if firstData[nextLink] = null then "" else linkUri[Scheme] & "://" & linkUri[Host] & linkUri[Path],
            query = if firstData[nextLink] = null then [] else linkUri[Query],
            hasCurrent = true,
            hasNext = firstData[hasNext]
        ],
        each  [hasCurrent], 
        (state) => 
            let
                nextList = retreiveList(state[baseUrl], "", state[query]),
                nextUri = if nextList[nextLink] = null then "" else Uri.Parts(nextList[nextLink]),
                hasCurrent = state[hasNext],
                hasNext = Logical.From(nextList[hasNext])
            in
                [
                    data = nextList[result],
                    baseUrl = if nextList[nextLink] = null then "" else nextUri[Scheme] & "://" & nextUri[Host] & nextUri[Path],
                    query = if nextList[nextLink] = null then [] else nextUri[Query], 
                    hasCurrent = hasCurrent,
                    hasNext = hasNext
                ],
        each [data]
    ),
    result = Table.Combine(retreivedPagenationList)
in
    result
    // retreiveList(siteUrl, relativePath, [
                    //         #"$skiptoken" = "Paged=TRUE&p_ID=0",
                    //         #"$top" = "2"
                    // ])

あとがき

デバッグがしにくいけど、なんか良い方法があるのだろうか？