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?
