Forum Discussion
Help on using search_after using a value such as report_number
Hi Anonymous ,
You're correct in your understanding: to retrieve all results from OpenFDA’s API, you would need to implement pagination using the search_after parameter, based on the last MDR_REPORT_KEY (or safetyreportid) from each page. Power Query can support this approach, but it typically requires defining a custom function and looping logic to continue fetching data until there are no further results.However, since this API is external and we’re unable to reproduce or validate its behavior from our side, we're limited in providing a complete tested implementation specific to this endpoint. That said, this is a common pattern in Power Query, and many community-driven solutions exist to paginate using search_after by dynamically updating query parameters.
As a next step, I recommend either:
- Engaging a Power BI developer or internal technical resource who can help implement this pattern, or
- Referring to community samples around OpenFDA + Power Query pagination these often provide reusable templates with minimal adjustments.
If you're looking for learning resources or generic pagination patterns using
List.Generate - PowerQuery M | Microsoft Learn
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Thank you,
Menaka.
Hi Anonymous ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
openFDA doesn’t let you just go “page 1, page 2, page 3” like a normal website. Instead, you have to ask for the first batch of results (up to 1000), then use a special trick to get the next batch.
Once try the below:
Start by asking the API for the first 1000 results. At the bottom of that list, grab the last report_number. Then ask the API, "Hey, give me the next 1000 results after that report number". Keep doing that until there are no more results left.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Hi v-menakakota,
I am assuming you mean to use search_after for the last lets say report_number so the 1000th one.
It would not be something I can specify beforehand obviously as I need to be looking across multiple pages.
Could I just ask as I have tried using POSTMAN - on if I could use links instead here?
For example, POSTman in the headers section retrieves the link shown above, which will conduct pagination on a web based api search.
Obviously implementing it in PowerQuery is its own challenge