Connection error with Standard Business Central API (pagination)

[NDM]

Hi all,

I'm working with Business Central and I use Power Bi to do reporting on the data.

The connection with a bearer token works perfect, but the result of this API is always 20.000 rows because this is the 'limit' per page for Business Central.

So for now I only receive 20.000 rows in my model, can anyone help me to fetch the rest of the data please ?

here the code I use:

let
// Token
AccessToken = Acces_Token(),

// Start Date and End Date
StartDateText = Date.ToText(RangeStart, "yyyy-MM-dd"),
EndDate = DateTime.Date(DateTime.LocalNow()),

// Construct the initial query string
QueryString = "?$filter=postingDate ge " & StartDateText & ",

// Define API Base URL and Relative Path
BaseUrl = "https://api.businesscentral......../",
RelativePath = "API OF BUSINESS CENTRAL",

// Function to fetch data from the API, handling pagination
GetData = (Url as text, AccumulatedResults as list) =>
let
// Request the data from the API
Response = try Json.Document(Web.Contents(BaseUrl, [
RelativePath = Url,
Headers = [Authorization = "Bearer " & AccessToken]
])) otherwise null,

// Extract the records from the API response
Records = if Response <> null and Record.HasFields(Response, "value") then Response[value] else {},

// Accumulate the results from the current page
NewAccumulatedResults = List.Combine({AccumulatedResults, Records}),

// Check if there's a next page (nextLink)
NextLink = if Response <> null and Record.HasFields(Response, "odata.nextLink") then Text.Replace(Response[odata.nextLink], BaseUrl, "") else null
in
// If there is a next page, recursively fetch the next set of data
if NextLink <> null then @GetData(NextLink, NewAccumulatedResults) else NewAccumulatedResults,

// Initial API request with the filter for the start date and top limit
FullUrl = RelativePath & QueryString,

// Fetch all data recursively
AllResults = GetData(FullUrl, {}),

// Convert the results into a table
TableResult = Table.FromRecords(AllResults)
in
TableResult

Thanks in advance

[v-echaithra]

Hi @NDM ,

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 E.

[v-echaithra]

Hi @NDM ,

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 E.

[v-echaithra]

Hi @NDM ,

There is no direct way for it but you can fetch the data in smaller chunks using $top and $skip parameters, or consider alternative methods like batch requests,
For example:

First request: $top=10000&$skip=0
Second request: $top=10000&$skip=10000
Third request: $top=10000&$skip=20000

Reference: Paging Microsoft Graph data in your app - Microsoft Graph | Microsoft Learn

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra E

[NDM]

Hi Chaithra,

thanks for your response, but isn't there a way to import more then 20.000 rows ??

[v-echaithra · Accepted solution]

Hi @NDM ,

Business Central API has a default row limit of 20,000 per page.

odata.maxpagesize can't be greater than the ODataServicesMaxPageSize server setting for on-premises and 20000 for online.

Reference: Server-Driven Paging in OData Web Services - Business Central | Microsoft Learn
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thanks and Regards
Chaithra