Handling Paginated JSON from API

[Nathaniel1307]

Hi All,

I'm trying to get some data from an API that returns paginated data in JSON format from a POST request.
Query I have so far is below which retrives the first 500 records but I'm struggling to come up with a way to retrieve the other records, the JSON reponse returns has_more = TRUE when there are other pages to retrieve and other pages can be retrieved by incrementing the page number in the content of the request.

Any help much appreciated.

URL, Site ID and API Key removed for security

let
    url = "https://URL/api/reports/ID",
    headers = [#"Content-Type" = "application/json", #"X-Site-ID" = "SITEID", #"X-Api-Key" = "APIKEY"],
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = Json.FromValue([page = 1])
        ]
    ),
    jsonResponse = Json.Document(response),
    data = jsonResponse[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "Student ID", "First Name", "Last Name", "Year of Study", "Date", "Product Name", "Total Price", "VAT", "Net", "Entered", "Status", "Refund Date", "Event Date", "Event Title"}, {"id", "Student ID", "First Name", "Last Name", "Year of Study", "Date", "Product Name", "Total Price", "VAT", "Net", "Entered", "Status", "Refund Date", "Event Date", "Event Title"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Date2", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Date])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date2", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date2", "Date"}})
in
    #"Renamed Columns"

[burakkaragoz · Accepted solution]

Hi @Nathaniel1307 ,

You're on the right track. When working with paginated APIs in Power Query, especially ones that use a has_more flag and page numbers, you’ll need to implement a recursive or loop-based function to fetch all pages.

Here’s a simplified approach using a custom function:

1. Create a function to fetch a single page:

let
    GetPage = (page as number) =>
    let
        url = "https://URL/api/report/87",
        headers = [
            #"Content-Type" = "application/json",
            #"Site-ID" = "SITEID",
            #"API-Key" = "APIKEY"
        ],
        body = Json.FromValue([page = page]),
        response = Web.Contents(url, [Headers = headers, Content = body]),
        json = Json.Document(response),
        data = json[data],
        hasMore = json[has_more]
    in
        [Items = data[items], HasMore = hasMore]
in
    GetPage

2. Use a loop to fetch all pages:

let
    GetAllPages = List.Generate(
        () => [Page = 1, Result = GetPage(1)],
        each [Result][HasMore] = true,
        each [Page = [Page] + 1, Result = GetPage([Page] + 1)],
        each [Result][Items]
    ),
    Combined = List.Combine(GetAllPages),
    TableOut = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    TableOut

This will keep calling the API until has_more is false and combine all the results into one table.

Let me know if you need help adapting this to your exact schema.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[v-echaithra]

Hi @Nathaniel1307 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Nathaniel1307 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @Nathaniel1307 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithra.

[burakkaragoz · Accepted solution]

Hi @Nathaniel1307 ,

You're on the right track. When working with paginated APIs in Power Query, especially ones that use a has_more flag and page numbers, you’ll need to implement a recursive or loop-based function to fetch all pages.

Here’s a simplified approach using a custom function:

1. Create a function to fetch a single page:

let
    GetPage = (page as number) =>
    let
        url = "https://URL/api/report/87",
        headers = [
            #"Content-Type" = "application/json",
            #"Site-ID" = "SITEID",
            #"API-Key" = "APIKEY"
        ],
        body = Json.FromValue([page = page]),
        response = Web.Contents(url, [Headers = headers, Content = body]),
        json = Json.Document(response),
        data = json[data],
        hasMore = json[has_more]
    in
        [Items = data[items], HasMore = hasMore]
in
    GetPage

2. Use a loop to fetch all pages:

let
    GetAllPages = List.Generate(
        () => [Page = 1, Result = GetPage(1)],
        each [Result][HasMore] = true,
        each [Page = [Page] + 1, Result = GetPage([Page] + 1)],
        each [Result][Items]
    ),
    Combined = List.Combine(GetAllPages),
    TableOut = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    TableOut

This will keep calling the API until has_more is false and combine all the results into one table.

Let me know if you need help adapting this to your exact schema.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[Nathaniel1307]

Hi @burakkaragoz 

I've tried out your code but am getting the below error:

An error occurred in the ‘GetPage’ query. Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=items

[burakkaragoz]

@Nathaniel1307 ,

No problem at all, glad you’re getting the data now. For the supply field, you’re right—Power Query does always supply blank if the column isn’t found in the input JSON.

If you want to handle this case, you can add some logic to check if the column exists before you try to expand it. One way is to use Table.HasColumns before expanding. Here’s a quick example:

let Source = ..., JsonData = ..., // Check if "supply" exists HasSupply = Table.HasColumns(JsonData, "supply"), Expanded = if HasSupply then Table.ExpandRecordColumn(JsonData, "supply", {"amount"}, {"supply.amount"}) else JsonData in Expanded

With this, you’ll only try to expand “supply” if it’s actually there. If not, the step will just skip expansion and you won’t get blanks or errors.

[Nathaniel1307]

Hi @burakkaragoz 

I don't quite understand where we are expanding or where supply comes from?

[v-echaithra · Accepted solution]

Hi @Nathaniel1307 ,

Thanks @burakkaragoz ,
Thank you for the update, @burakkaragoz  is explaining how to avoid errors when expanding a field that might not exist in the JSON response.
The expansion process in Power Query refers to when we extract fields from nested JSON structures like records or lists into separate columns.
In this case, we are expanding:
The data items field from JSON into a Power Query table.
The "supply" field if it exists inside the extracted records.
Supply field is an example scenario. The example checks if the "supply" field is present using Table.HasColumns(). If "supply" exists, it expands the "amount" column inside it. If it doesn’t exist, the step is skipped to prevent errors or empty columns. This approach ensures Power Query handles missing fields safely without breaking the transformation process. In your case you can use any other field from your table.

Best regards,
Chaithra.