REST API pull with pagination

[paulmorgan]

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"

[v-shex-msft · Accepted solution]

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

[lbendlin]

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.

[paulmorgan]

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