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
Hi Anonymous ,
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.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.
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"