Power BI/Power Query: API Response Pagination Part 3 - Missing Indicator

This is the 3rd post about pagination in Power BI/Power Query.

In part 1 and part 2 we have discussed how to paginate Restful response if the response contains a total item Count or a next page Token. So what if the response only returns us records, without any indicator about the next page?

Let's take a look on Basecamp todo API. If we call the API at the end point /api/v1/projects/{project id}/todos.json?page=1, the begin of the response and the end of the response look like below screenshots:

begin of the response

end of the response

Sorry for the messy screenshots, but the point is, this API doesn't give us a token for next page. We have to query the next page to ensure our API call pulls all records we need. You might have hundreds of ways to do the job, but my first impression is let's create a recursive function. Let's think about the problem:

1. We receive the first page from the response and merge the records into a table "T";
2. Then we move on to the next page
2.1 If the next page contains records then we repeat step 1 (merge records into "T") and step 2
2.2 If there is no records in the next page, then we stop and return the table "T"

It is simply a classic recursive scenario. In Power Query, the recursive function is all about the "@". Let's look at the codes

let
    ufnQuery = (n) =>
        let
            jsonDoc= Json.Document(
                        Web.Contents(
                            "https://basecamp.com/{domain}/api/v1/projects/{project id}/todos.json?page=" & Number.ToText(n), 
                            [Headers=[Authorization="Basic {the key}"]]
                        )
        ),
        tmpTbl = Table.FromList(jsonDoc, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        tmpTbl,

    fnRecursive = (tbl, n) =>
        if Table.RowCount(ufnQuery(n)) > 0 then @fnRecursive(Table.Combine({tbl, ufnQuery(n)}), n+1) else tbl,

    tbl = ufnQuery(1),
    result = fnRecursive(tbl, 2)  
in
   result

As you can see, the key function in codes above is the 2nd function, in the function body, we check amount of records returned from the call, and if the amount is greater than 0, we recursively (@) call the function by passing the merged table and next page number. Simple enough, right?