Help on using search_after using a value such as report_number

[Justin326117]

Hi I am looking to pull in API results from the openFDA 

An example API call would be api.fda.gov/device/event.json?search=date_received:[20250101+TO+20250201] lets say

I am trying to find how I can pull in all the data from this API call, I did look at using skip / limit to paginate, however I did not find those parameters listed as ways to do pagination from the OpenFDA website.

Instead they specify using search_after for a URL.

This url does not apear in the API calls, so someone suggested a custom connector to kind of pull in the URL link for me to use, however I feel that would be too complicated.

could I not just find the last report_number value from the first page, then use this value with search after to find the second page of values where for each the limit would be 1000.

I would assume when this gets to the last page, it would just stop 

Just finding this process very annoying in finding how I can get all my data to actually come through 

[v-menakakota]

Hi @Justin326117 ,

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.

[Justin326117]

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?

<https://api.fda.gov/device/event.json?search=date_manufacturer_received%3A%5B20250101%20TO%202025030...>; rel="next"

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

[v-menakakota]

Hi @Justin326117 ,

You're correct, the search_after parameter in OpenFDA works by using a sorted field, such as report_number, and passing the last value from the previous response to get the next set of results. This method must be used together with a sort parameter (e.g., sort=report_number:asc) for it to work properly.

Regarding the rel="next" link in the response headers: while it's valid for navigating pages in tools like Postman, Power Query does not natively support reading response headers, so it's not a practical option there.

For Power Query, we recommend using the search_after approach, as it is fully URL-based and aligns with how Power BI handles web data sources. You would need to loop through pages by retrieving the last report_number from each response and passing it into the next request until no more data is returned.

Hope this clarifies the approach, feel free to reach out if you need further information.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[v-menakakota]

Hi @jabrillo ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Justin326117]

Hi @v-menakakota 

Sadly I am totally new to PowerQuery - have never coded, so just do not know how to implement all of this 

All I really want is for it to find the last MDR_REPORT_KEY value and search after that but I do not know how to code that into PowerQuery

let
Source = Json.Document(Web.Contents("#(tab)https://api.fda.gov/drug/event.json?search=receivedate:[20040101+TO+20081231]&limit=1000")),

#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded meta" = Table.ExpandRecordColumn(#"Converted to Table", "meta", {"disclaimer", "terms", "license", "last_updated", "results"}, {"meta.disclaimer", "meta.terms", "meta.license", "meta.last_updated", "meta.results"}),
#"Expanded meta.results" = Table.ExpandRecordColumn(#"Expanded meta", "meta.results", {"skip", "limit", "total"}, {"meta.results.skip", "meta.results.limit", "meta.results.total"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded meta.results", "results"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"safetyreportid", "transmissiondateformat", "transmissiondate", "serious", "seriousnessdeath", "seriousnesslifethreatening", "seriousnesshospitalization", "seriousnessother", "seriousnessdisabling", "seriousnesscongenitalanomali", "receivedateformat", "receivedate", "receiptdateformat", "receiptdate", "fulfillexpeditecriteria", "companynumb", "primarysource", "sender", "receiver", "patient"}, {"results.safetyreportid", "results.transmissiondateformat", "results.transmissiondate", "results.serious", "results.seriousnessdeath", "results.seriousnesslifethreatening", "results.seriousnesshospitalization", "results.seriousnessother", "results.seriousnessdisabling", "results.seriousnesscongenitalanomali", "results.receivedateformat", "results.receivedate", "results.receiptdateformat", "results.receiptdate", "results.fulfillexpeditecriteria", "results.companynumb", "results.primarysource", "results.sender", "results.receiver", "results.patient"}),
#"Expanded results.sender" = Table.ExpandRecordColumn(#"Expanded results1", "results.sender", {"senderorganization"}, {"results.sender.senderorganization"}),
#"Expanded results.patient" = Table.ExpandRecordColumn(#"Expanded results.sender", "results.patient", {"patientonsetage", "patientonsetageunit", "patientweight", "patientsex", "patientdeath", "reaction", "drug"}, {"results.patient.patientonsetage", "results.patient.patientonsetageunit", "results.patient.patientweight", "results.patient.patientsex", "results.patient.patientdeath", "results.patient.reaction", "results.patient.drug"}),
#"Expanded results.patient.patientdeath" = Table.ExpandRecordColumn(#"Expanded results.patient", "results.patient.patientdeath", {"patientdeathdateformat", "patientdeathdate"}, {"results.patient.patientdeath.patientdeathdateformat", "results.patient.patientdeath.patientdeathdate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded results.patient.patientdeath",{{"meta.disclaimer", type text}, {"meta.terms", type text}, {"meta.license", type text}, {"meta.last_updated", type date}, {"meta.results.skip", Int64.Type}, {"meta.results.limit", Int64.Type}, {"meta.results.total", Int64.Type}, {"results.safetyreportid", type text}, {"results.transmissiondateformat", Int64.Type}, {"results.transmissiondate", Int64.Type}, {"results.serious", Int64.Type}, {"results.seriousnessdeath", Int64.Type}, {"results.seriousnesslifethreatening", Int64.Type}, {"results.seriousnesshospitalization", Int64.Type}, {"results.seriousnessother", Int64.Type}, {"results.seriousnessdisabling", Int64.Type}, {"results.seriousnesscongenitalanomali", type any}, {"results.receivedateformat", Int64.Type}, {"results.receivedate", Int64.Type}, {"results.receiptdateformat", Int64.Type}, {"results.receiptdate", Int64.Type}, {"results.fulfillexpeditecriteria", Int64.Type}, {"results.companynumb", type text}, {"results.primarysource", type any}, {"results.sender.senderorganization", type text}, {"results.receiver", type any}, {"results.patient.patientonsetage", Int64.Type}, {"results.patient.patientonsetageunit", Int64.Type}, {"results.patient.patientweight", type number}, {"results.patient.patientsex", Int64.Type}, {"results.patient.patientdeath.patientdeathdateformat", type any}, {"results.patient.patientdeath.patientdeathdate", type any}, {"results.patient.reaction", type any}, {"results.patient.drug", type any}}),
#"results patient reaction" = #"Changed Type"{15}[results.patient.reaction],
#"results patient reaction1" = #"results patient reaction"{4}
in
#"results patient reaction1"

[v-menakakota · Accepted solution]

Hi @Justin326117 ,

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.