topic REST API pull with pagination in Developer

REST API pull with pagination

paulmorgan — Fri, 14 Aug 2020 18:01:13 GMT

I'm fairly new using APIs for building a Power BI report. I'm sure there is an easy way to do this, but I don't know how to proceed.

I have a single pull from a REST API.

Each page in the response is 1000 rows long. I need to figure out what type of Do/While loop to insert into the Advanced Editor so that it pulls 4 pages (my output is just over 3,000 rows).

Just using pageIndex=1 and pageSize=1000 (hardcoded), after converting to a table inside the Advanced Editor, I get the text below. What should I insert into this so that I pull however many pages are required to get the full output?

Thank you in adance.

let
Source = Json.Document(Web.Contents("https://api.connect.company.com/v1/retrieve?pageIndex=1&pageSize=1000")),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"geography_name", "geography_id", "region_name", "module_id", "module_name", "time_period_formatted", "time_period", "value", "rating_name", "parent_rating_name"}, {"geography_name", "geography_id", "region_name", "module_id", "module_name", "time_period_formatted", "time_period", "value", "rating_name", "parent_rating_name"})
in
#"Expanded Column1"

Re: REST API pull with pagination

lbendlin — Fri, 14 Aug 2020 23:53:06 GMT

Use a recursive function

getdata(offset,count)

Then inside that function do your API call. If the result (returned rows) is the same as the count, increase the offset by one and call the function again. If it is less, pack up, and return the collected rows back to the calling process.

Re: REST API pull with pagination

paulmorgan — Mon, 17 Aug 2020 12:15:27 GMT

Thank you for taking the time to respond.

I'm not that familiar with how to do what you suggest.

However, I will send this suggestion to someone in our development team and ask for assistance on how to implement.

Thanks again

Re: REST API pull with pagination

Anonymous — Wed, 19 Aug 2020 07:08:18 GMT

Hi @paulmorgan,

You can take a look at the following document about the help functions about processing pagination api if it meet your requirement:

Helper Functions#tablegeneratebypage

Regards,

Xiaoxin Sheng