A function for retrieving data from the USDA Quick Stats API

resources.txt

To view documentation for the API and to request an API Key:

https://quickstats.nass.usda.gov/api/

USDAQuickStats-GetParameterAllowedValues.m

(param_name as text) =>
let
    url = "http://quickstats.nass.usda.gov/api/get_param_values/?",

    query = "param=" & param_name,

    Source = Json.Document(
        Web.Contents(url & query & "&format=JSON",
                    [ApiKeyName="key"])
    ),

    Result = Record.Field(Source, param_name)
in
    Result

USDAQuickStats-GetUSDAQuickStats.m

// Returns data from the API - this version uses the PrepareAPIQueryText function
(args as list) =>
let
    // builds the query text
    query = PrepareAPIQueryText(args),

    // Retrieves the data from the API
    Source = Json.Document(
        Web.Contents(
            "https://quickstats.nass.usda.gov/api/api_GET/?" & query & "&format=JSON", 
            [ApiKeyName="key"]
        )
    ),

    // Convert the Json to toa table where each row is a record
    data = Table.FromList(Source[data], Splitter.SplitByNothing()),

    // Expand the records to columns
    expand = Table.ExpandRecordColumn(data, "Column1", output_columns)
in
    expand

USDAQuickStats-GetUSDAQuickStatsv2.m

// Returns data from the API - this version uses the PrepareAPIQueryRecord function
(args as list) =>
let

    query =  Record.AddField( PrepareAPIQueryRecord(args) , "format", "JSON"),

    // Retrieves the data from the API
    Source = Json.Document(
        Web.Contents(
            "https://quickstats.nass.usda.gov/api/api_GET/?", 
            [ApiKeyName="key", Query=query]
        )
    ),

    // Convert the Json to toa table where each row is a record
    data = Table.FromList(Source[data], Splitter.SplitByNothing()),

    // Expand the records to columns
    expand = Table.ExpandRecordColumn(data, "Column1", output_columns)
in
    expand

USDAQuickStats-PrepareAPIQueryRecord.m

// converts the arguments to a record suitable for use as the Query field value in 
// the Web.Contents options record
// Used by GetUSDAQuickStatsv2
(args as list) as record =>
let
    /*
    args is a list of lists where each sub-list is a {param, values} pair
    e.g.
    args = {
        {"commodity_desc",{"CORN","WHEAT"}},
        {"year",{"2002","2012","2022"}},
        {"domain_desc",{"AREA HARVESTED"}}
    },
    */

    query = [],

    // for each sub-list, add a field to the record
    add_record_fields = List.Accumulate(
        args,
        [],
        (a,b) => Record.AddField(a,b{0},b{1})
    ),

    result = add_record_fields
in
    result

USDAQuickStats-PrepareAPIQueryText.m

// converts the arguments to a query string that can be concatenated with the API URL
// Used by GetUSDAQuickStats
(args as list) as text =>
let
    /*
    args is a list of lists where each sub-list is a {param, values} pair
    e.g.
    args = {
        {"commodity_desc",{"CORN","WHEAT"}},
        {"year",{"2002","2012","2022"}},
        {"domain_desc",{"AREA HARVESTED"}}
    },
    */

    // takes one of the sub-lists above and converts it into a param string
    fn_build_query_string = (arg as list) => 
        let
            query_param = arg{0}, // the column name to filter on
            data = arg{1} // the values for this column
        in 
            try Text.Combine( List.Transform(data, each "&" & query_param & "=" & _) ) otherwise "",

    // applies the function to the args
    query_params = List.Transform(
        args,
        fn_build_query_string
    ),

    // combines the args into a single query and removes the leading ampersand
    query = Text.Range( Text.Combine(query_params) , 1)
in
    query