Get data - Json.document

[HCJ2026]

Hi,
I'm trying to get data from an API to our system AutoPilot.

When I use this promt:

 Json.Document(

        Web.Contents(

            "https://api.autopilot.dk/v2/Timeregistrations",

            [

                Headers = [

                    #"accept" = "application/json",

                    #"Content-Type" = "application/json",

                    #"APPartnerKey" =

                    #"APCompanyKey" =

                ],

                Content = Text.ToBinary("{""page"":1,""pageSize"":29999}")

            ]

        )

    )
I only get the one page of date - but I would like to get all data.
Can someone assist me in getting that to happend?

[Olufemi7]

Hello @HCJ2026, 

This is expected behaviour the AutoPilot API is paginated.

Your query explicitly requests only page 1, so Power Query will return just that page. Power Query does not automatically fetch all pages; you must loop through them in M.

You need to implement pagination, for example with List.Generate:

let
    BaseUrl = "https://api.autopilot.dk/v2/Timeregistrations",

    GetPage = (Page as number) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Headers = [
                        #"accept" = "application/json",
                        #"Content-Type" = "application/json",
                        #"APPartnerKey" = "YOUR_KEY",
                        #"APCompanyKey" = "YOUR_KEY"
                    ],
                    Content = Text.ToBinary(
                        "{""page"":" & Number.ToText(Page) & ",""pageSize"":29999}"
                    )
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Page = 1, Data = GetPage(1)],
            each List.Count([Data]) > 0,
            each [Page = [Page] + 1, Data = GetPage([Page])],
            each [Data]
        ),

    Combined = List.Combine(Pages),
    Result = Table.FromRecords(Combined)
in
    Result

Microsoft docs confirm that REST APIs must be paged manually in Power Query and all pages must be explicitly combined: Paging 

[HCJ2026]

Thank you very much for your response.
I still get this error when I try to set it up:

[Olufemi7]

Hello @HCJ2026, 

The 500 error suggests the API is rejecting the request structure rather than the pagination logic itself.

In Power Query, when Content is included in Web.Contents, the request is automatically sent as POST. If this endpoint expects a GET request with query parameters instead, that can result in a 500 error.

Try switching from Content to Query and reduce the page size to something smaller (e.g., 500):

let
    BaseUrl = "https://api.autopilot.dk/v2/Timeregistrations",

    GetPage = (Page as number) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Headers = [
                        #"accept" = "application/json",
                        #"APPartnerKey" = "YOUR_KEY",
                        #"APCompanyKey" = "YOUR_KEY"
                    ],
                    Query = [
                        page = Number.ToText(Page),
                        pageSize = "500"
                    ]
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Page = 1, Data = GetPage(1)],
            each List.Count([Data]) > 0,
            each [Page = [Page] + 1, Data = GetPage([Page])],
            each [Data]
        ),

    Combined = List.Combine(Pages),
    Result = Table.FromRecords(Combined)

in
    Result

If the API response is wrapped (for example { data = [...] }), the loop condition would need to reference that field instead.

Could you confirm whether the endpoint is documented as GET or POST?

[HCJ2026]

Thank you very much for your quick responce.

It seems to me that the endpoint is POST

And I get this error when I enter the expression:

Expression.Error: We can't convert the value "let BaseUrl = "..." to the type Record. Details: Value=let BaseUrl = "https://api.autopilot.dk/v2/Timeregistrations", GetPage = (Page as number) => Json.Document( Web.Contents( BaseUrl, [ Headers = [ #"accept" = "application/json", #"APPartnerKey" = "80df34a5-ca67-4883-bfb5-bcb8d624ee9f", #"APCompanyKey" = "cdbcf182-ec4d-4ab7-9557-beac64507129" ], Query = [ page = Number.ToText(Page), pageSize = "500" ] ] ) ), Pages = List.Generate( () => [Page = 1, Data = GetPage(1)], each List.Count([Data]) > 0, each [Page = [Page] + 1, Data = GetPage([Page])], each [Data] ), Combined = List.Combine(Pages), Result = Table.FromRecords(Combined) in Result Type=[Type]

[Olufemi7]

Hello @HCJ2026, 

The error you are seeing:

Expression.Error: We can't convert the value "let BaseUrl = ..." to the type Record

usually happens when the full M query is pasted into a place that expects a record or step, not a full query.

Please open Power Query → Advanced Editor and replace the existing query with the code below.

Since the AutoPilot endpoint is POST and paginated, Power Query must request each page and combine them.

let
    BaseUrl = "https://api.autopilot.dk/v2/Timeregistrations",

    GetPage = (Page as number) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Headers = [
                        #"accept" = "application/json",
                        #"Content-Type" = "application/json",
                        #"APPartnerKey" = "YOUR_PARTNER_KEY",
                        #"APCompanyKey" = "YOUR_COMPANY_KEY"
                    ],
                    Content =
                        Text.ToBinary(
                            "{""page"":" & Number.ToText(Page) &
                            ",""pageSize"":500}"
                        )
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Page = 1, Data = GetPage(1)],
            each List.Count([Data]) > 0,
            each [Page = [Page] + 1, Data = GetPage([Page])],
            each [Data]
        ),

    Combined = List.Combine(Pages),
    Result = Table.FromRecords(Combined)
in
    Result

Notes:

• This must be pasted in Advanced Editor, not inside a step.

• The API returns one page at a time, so pagination is required.

• Using a smaller pageSize (e.g., 500–1000) helps avoid API errors.

[v-sshirivolu]

Hi @HCJ2026 ,

I would also take a moment to thank @Olufemi7 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[HCJ2026]

Hi @Olufemi7  & @v-sshirivolu 
Thank you very much for your quick response to my request. 
Yesterday we finaly succeded in getting the query correct and the I now get the expected data in my datamodel - thank you very much for your input and support.

[v-sshirivolu]

Hi @HCJ2026,

Your current API request is set to fetch page 1 with a fixed pageSize of 29,999 records. Most modern APIs, including AutoPilot, use pagination, so each request only returns a single page of data even if more pages are available. This is why you only receive the first page. To get all records, you’ll need to add pagination logic and request each page in order, then combine the results. In Power Query (M language), this usually involves creating a function to retrieve one page and then using List.Generate or similar logic to loop through page numbers until no more data is returned.
After collecting all pages, you can combine them into one table. Json.Document itself does not require any changes, rather, you need to add pagination logic so Power Query makes multiple API calls. This way, your query will return all timeregistrations, not just the first page.

Refer these Microsoft Docs - 

https://learn.microsoft.com/en-us/powerquery-m/web-contents

https://learn.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme

https://learn.microsoft.com/en-us/power-query/handling-paging

Thank you.