1

I've been searching the internet will no success.

I'm trying to retrieve all rows (62k+ rows), but the call only returns the first 1000

Does anyone know of a parameter that needs setting to increase the 1000 limit, or how to make repeated calls until all rows are returned

The Power Query Editor displays the following
totalResults 62027
startIndex 1
itemsPerPage 1000

Query from Advanced editor
let
Source = Json.Document(Web.Contents("removed web address"))
in
Source

SteveC
  • 11
  • 2
  • Here's a [related question](https://community.powerbi.com/t5/Power-Query/API-calls-with-Pagination/m-p/323080) in the Microsoft Community. – D M Jan 08 '21 at 14:43
  • There are a few limits when it comes to using the REST API in BI. You can see if any of these are limiting your response? https://learn.microsoft.com/en-us/power-bi/developer/automation/api-rest-api-limitations – alpharomeo Jan 08 '21 at 14:44

1 Answers1

0

I just happened to encounter the same problem. The following code should be adaptable to work for you too.

I made a function to access the API:

let API = (relPath as text, optional queries as nullable record) =>
    let
        Source = Json.Document(Web.Contents("https://api.com/", 
            [ Query = queries, 
              RelativePath=relPath]))
    in
        Source
in 
    API

I can then retrieve the records in another query using List.Generate to perform multiple calls to the API as required.

let
    Source = API("path/to/records", [rows_per_page="1000"]),
    pages = Source[total_pages],
    records = 
        if pages = 1 then Source[records] 
        else List.Combine(List.Generate(
            () => [page = 1, records = Source[records]], 
            each [page] <= pages, 
            (x) => [page = x[page] + 1, records = API("path/to/records", [page = Text.From(x[page] + 1), rows_per_page = "1000"])[records]], 
            each [records]))
in
    records
tranquillity
  • 1,619
  • 1
  • 5
  • 12