Trying to understand api page limits in m-code

[Joris_NL]

Hello,

I have a WORKING pbi file which retrieves some tables from an web api.

My colleague and me had a problem at first, because the api only gave a sample of 100 records of a table. So he had ChatGPT write some code retrieve the 100 records a number of times and create a full table.

Initially, the code had a MaxLimit=1000 (not 10,000). But for tables with more than a couple thousand records, I would miss some data. So I increased this to 10,000 and get full tables (with 30,000 records for example). It takes a lot longer to refresh though.

This all raises a lot of questions.

- How does the code work? It generates 10,000 times 100 records, right? So a million records?

- Why is MaxLimit=1000 not enough for a table with 30,000 records?

- Will 10,000 always be enough for bigger tables?

Thanks in advance!!!!!!!!

let
BaseURL = "https://api.etc.etc.etc",
PageSize = 100,
MaxLimit = 10000,
PagesToFetch = List.Generate(
()=> [PageNumber=1, Offset=0],
each [Offset] < MaxLimit,
each [PageNumber=[PageNumber]+1, Offset=[Offset]+PageSize]

),
AllData = List.Transform(PagesToFetch, (page) =>
let
Response = Json.Document(
Web.Contents(
BaseURL &
"?offset=" & Text.From(page[Offset]) &
"&limit=" & Text.From(PageSize),
[Headers=[#"Content-Type"="application/json"]]
)
)
in
Response
),
CombinedData = Table.FromList(AllData, Splitter.SplitByNothing()),

[Joris_NL]

Thanks v-rongtiep-msft!

I still don't understand though. If it makes 'a number of requests up to 10,000 records', how can I end up with tables that are 30,000?

I guess I can't use a some detection code to request only the number of records that the api has available? To save time.

Joris

[Anonymous]

Hi @Joris_NL ,

Understanding How the Code Works:

The code you've shared uses a pagination technique to fetch data from the API in chunks of 100 records at a time until it reaches the you've specified. The function is key here; it creates a list of pages to fetch based on the and . However, it doesn't generate 10,000 times 100 records. Instead, it fetches 100 records per request until the total number of records fetched reaches the . If is 10,000, it means the code will make up to 100 requests (10,000/100) if necessary, not a million.

Why MaxLimit=1000 Was Not Enough:

The parameter essentially controls the maximum number of records the code attempts to fetch from the API. If your table has more than a couple thousand records, setting to 1000 means the code will stop fetching new data after 1000 records, hence missing out on additional data. This is why increasing it to 10,000 helped you retrieve larger tables.

Will 10,000 Always Be Enough?:

Whether a of 10,000 will always be sufficient depends on the size of the tables you're trying to fetch. For tables larger than 10,000 records, you might still miss some data. It's a balance between ensuring you fetch all necessary data and managing the time it takes to refresh your dataset. Increasing the can significantly increase refresh times, as you've noticed.

More details: Paging Microsoft Graph data in your app - Microsoft Graph | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.