Pagination with dynamic pages

[ThePokyWizard]

Recently, by following this tutorial, I managed to get PowerBI to generate a complete list with all the pages. The API I access works similarly to the one in the tutorial; however, it has parameters that list the most recent data. Thus, in one minute, I might have 5 pages, and in the next, I might have only 1 or even no results found.

In this model, a list is created with a fixed number of pages, but if I update the query, there may be no updated data, resulting in a 404 error when trying to navigate to a non-existent page, or even not navigating to the next page if it exceeds the original pages.

How can I create something more dynamic?

Additionally, the API I use returns an integer indicating the number of the next page, not just the total number of pages.

My JSON consists of this format:

/** Current Page 01 **/
{
	size: 1024,
	pages: 21,
	next_page: 2,
	previous_page: null,
	data: [
		{...},
		{...},
		{...},
		{...}
	]
}

/** Current Page 21 **/
{
	size: 1024,
	pages: 21,
	next_page: null,
	previous_page: null,
	data: [
		{...},
		{...},
		{...},
		{...}
	]
}

My URL follows this pattern:

https://example.com/api/?minutesSinceLastModification=15min&page=1
https://example.com/api/?minutesSinceLastModification=15min&page=2
https://example.com/api/?minutesSinceLastModification=15min&page=N
https://example.com/api/?minutesSinceLastModification=15min&page=1024

[Sahir_Maharaj · Accepted solution]

Hello @ThePokyWizard,

Can you please try the following:

Function to Fetch Data

let
    FetchData = (page as number) as record =>
    let
        Source = Json.Document(Web.Contents("https://example.com/api/?minutesSinceLastModification=15min&page=" & Number.ToText(page))),
        Data = Source[data],
        NextPage = Source[next_page],
        Result = [Data = Data, NextPage = NextPage]
    in
        Result
in
    FetchData

Query to Fetch All Pages

let
    Source = (page as number) =>
    let
        Fetch = FetchData(page),
        Data = Fetch[Data],
        NextPage = Fetch[NextPage],
        GetAllData = if NextPage <> null then
                        Data & @Source(NextPage)
                     else
                        Data
    in
        GetAllData,

    // Start the pagination from page 1
    Data = Source(1)
in
    Data

Hope this helps.