Forum Discussion
Get data - Json.document
Thank you very much for your response.
I still get this error when I try to set it up:
Super UserHello 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?
Thank you very much for your quick responce.
It seems to me that the endpoint is POSTAnd 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 ResultNotes:
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.
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