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.
Community SupportHi @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"
- v-menakakota
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.