none
FDA Api Limitations RRS feed

  • Question

  • i am trying to create a dataset using the relatively new openfda website. Initially i was running into security errors when attempting to access the api directly so I downloaded the static data set they offer for food recalls (the data i'm interested in) and cleansed it with excel and eventually brought it into power bi for some really great reports. the issue is my boss would love for this dataset to be dynamic so I got through the security issue only to now run into another issue. Open FDA limits the number of records queried per minute per API key. Obviously i could just use the most recent 100 records but this would be nowhere near as informative as access to the tens of thousands of recalls they have posted. Any help on this would be great. I was thinking there may be some way to loop power query so that it only draws 100 records at a time and then delay each draw by a minute but if this is possible it is well beyond my abilities. Just for reference, the static dataset is about 11,000 records and my boss would like this to run off even more and run off this dynamically. Any help would be greatly appreciated.
    • Edited by AnthonyJY Friday, July 8, 2016 6:07 PM
    Thursday, July 7, 2016 7:41 PM

Answers

  • Hi Anthony. You can definitely do this in PQ. Here's an example which gets pages from a movie info website, with a one-second delay between page requests:

    let
        Source = {1..5},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageNumber"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Page", each Function.InvokeAfter(() => Web.Contents("http://www.filmcrave.com/list_genre_movie.php?genre=Family&page=" & Text.From([PageNumber])), #duration(0,0,0,1)))
    in
        #"Added Custom"

    Ehren

    Monday, July 11, 2016 10:57 PM
    Owner

All replies

  • Hi Anthony. You can definitely do this in PQ. Here's an example which gets pages from a movie info website, with a one-second delay between page requests:

    let
        Source = {1..5},
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageNumber"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Page", each Function.InvokeAfter(() => Web.Contents("http://www.filmcrave.com/list_genre_movie.php?genre=Family&page=" & Text.From([PageNumber])), #duration(0,0,0,1)))
    in
        #"Added Custom"

    Ehren

    Monday, July 11, 2016 10:57 PM
    Owner
  • Is #duration(0,0,0,1) where the one second delay is specified?
    Monday, July 18, 2016 8:32 PM
  • Yep. The numbers there are: days, hours, minutes, seconds.

    Ehren

    Monday, July 18, 2016 8:49 PM
    Owner
  • Thank you very much Ehren, this is very helpful.
    Tuesday, July 19, 2016 12:41 PM
  • Hi Anthony,

    I'm trying to do the exact same thing.


    Did you find a way to solve it?

    Thursday, March 14, 2019 11:57 PM