Forum Discussion
PowerQuery Post API call to return complete data from all pages.
Let's assume that in the particular search criteria my API returns 60,000 records and my API call will always allow 1000 records. Due to the sequential pagination logic, it took longer to load the entire data set. In the example attached, I can read the complete data. Let's say we have 60000 records and each API call will support 1000 records, so the total number of pages is 60000/1000=60 pages. If each 1000 records took 4 seconds to load, then it will take 240 seconds to load all the data. How can the dataload be made fast through parallel mechanisms.
Hi hanuraolm ,
I see the problem.
There is also a similar post regaring this: https://community.powerbi.com/t5/Power-Query/Power-Query-Parallel-or-Concurrent-REST-Invocation/td-p/2106491
Unfortunately there it states that "Parellel operations are not permitted within a single query" - not sure if it's still actual.
I would still try below potential solution (adding a column with starts and a second column to get the data), BUT if that doesn't work, as suggested also by the OP of the above question, I'd create an intermediery (local python, custom software hosted in a service etc.) that would make the requests in paralell and make them accessible to PowerQuery somehow.
Below potential solution 😊
Given that you know the start of all pages immediately after you run the first request, one option could be to FIRST create a column with the start of all pages, like this:
and THEN add a column that invokes getPageData using the above start column instead of List.Generate - maybe this will help:
The code to do this could look like:
... your code
maxPages = Number.RoundUp(totalRecords/1000),
pagesStartList = List.Generate(
() => 0,
(lastPage) => lastPage < maxPages,
(lastPage) => lastPage + 1,
(lastPage) => lastPage * 1000
),
pagesStartTable = Table.FromColumns({pagesStartList}, {"start"}),
// Define a function to retrieve data for a single page
getPageData = (start as number) =>
let
... your code
in
data,
#"Added Custom" = Table.AddColumn(pagesStartTable, "pageData", each getPageData([start]))
in
#"Added Custom"
I haven't tested it, but if it works, please do tell us if it did AND also mark this as ANSWER if it helped.