Incoming API with paginated data needed to be merged into Power BI report one table - Please advise

[Sunlightnsk]

Hello,

I am new to Power BI and would like to ask for advice, please.

I have an API that sending a data from the Service Now. The Service Now table has a heavy data (a few Gbs) so the pagination was used preventing the time out in Service now. I am using a pageIndex as a parameter. Each page shows a number of thousands of data and I have hundreds of pages total. 

I need to create a report in Power BI showing all of that data. Knowing that its coming in paginated, I need to use pageIndex parameter to make an automatic query of all the data (page-by-page) and merging into one table. I would not need that if the Service Now would not be bottlenecking because of the timeout.

Could you please advise a solution for that?

As an option, I created a blank request with this query:

= (pageIndex as number) =>
let
apiUrl = "https://***reportdata?pageIndex=" & Text.From(pageIndex),
responseData = Json.Document(Web.Contents(apiUrl))
in
responseData

And another query:

pageIndexes = {1..5} // I want to get the data for the first 5 pages for now.

I was thinking to using the 2nd query and Invoke custom function, but all the panel is grey for me, however, the query is active.

[Sunlightnsk · Accepted solution]

I was on the right way and almost made it, but this video helped me to complete the task:

https://www.youtube.com/watch?v=6tid-xh1t34

Watch from 15:45.