API query

[rmcgrath]

Hello...I am currently connecting to an API and trying to pull in data from 2015 to present (576,000 data points).  The API has a limit of 5,000 data points.  I would have to do 115+ queries to pull in the data.  Here is the method I am using so far (I hid some of the names with *s):

• We have 576226 data points since 2015. The maximum limit is 5000:
• E.g page 1 https://app.******.com/api/v1/data-direct/categories/****/historical-series-data-points?filters[0][b...
• Page 2 https://app.******.com/api/v1/data-direct/categories/****/historical-series-data-points?filters[0][by]=date&filters[0]...
• etc

Is there a more efficient/easier way to do this?

[rmcgrath]

I am connecting to an API that has 576000 data points and a limit of 5000.  Is there a more efficient way to grab the data than what I am currently doing? I will need to do 115+ queries!😯

• We have 576226 data points for **** since 2015. So you will have to use pagination. The maximum limit is 5000. I am sure PowerBI supports pagination, but do let me know if you have any issues with it.
• E.g page 1 • We have 576226 data points for Eggs since 2015. So you will have to use pagination. The maximum limit is 5000. I am sure PowerBI supports pagination, but do let me know if you have any issues with it.
  o E.g page 1 https://app.****.com/api/v1/data-direct/categories/****/historical-series-data-points?filters[0][by]...
  o Page 2 https://app.****.com/api/v1/data-direct/categories/****/historical-series-data-points?filters[0][by]...
  o etc
• etc

[v-hjannapu]

Hi @rmcgrath,

Thank you  for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @Deku , 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.

When working with REST APIs, data is often returned in pages when the dataset is too large to send all at once. Power Query can handle various pagination methods, but since each API handles paging differently, you'll usually need to adjust the sample logic to fit your specific case.

A reliable approach is to create a function that fetches one page of data, then recursively loops through the remaining pages until no more records are returned. This is especially useful when APIs enforce limitslike a 5,000-record page limit. By calling this function inside another query that combines all results, you automate the process and enable seamless refreshes in Power BI or Microsoft Fabric. Just be sure to adapt the API URL and JSON parsing to match your API’s structure.

Gothrough with below document which may help you to resolve the Issue
Helper functions for M extensions for Power Query connectors - Power Query | Microsoft Learn

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team

[Deku]

Please see this related post

https://community.fabric.microsoft.com/t5/Desktop/Pagination-of-a-REST-API-in-Power-Query-using-M/m-...

[Akash_Varuna]

Hi @rmcgrath Automate pagination using a script to iterate through pages or split queries by date ranges (e.g., year-by-year). Parallelize requests using libraries like concurrent.futures to speed up the process, ensuring compliance with API rate limits. Check if the API offers bulk export or aggregation options to reduce query load