API data expansion help

[Anonymous]

Hi - I'm attempting to expand the data from a USDA report; https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2498

I can get it into PowerQuery editor easily, but I cannot figure out how to expand the whole thing into the row level data. I get here: 

Then convert it to a table, but then I'm stuck. Any next step gives me a list of values, rather than the dataset that I'm looking for. 

I would really appreciate any help. 

Thanks in advance~ 

[ppm1]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Json.Document(Web.Contents("https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2498")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded stats" = Table.ExpandRecordColumn(#"Converted to Table", "stats", {"totalRows:", "returnedRows:", "userAllowedRows:"}, {"stats.totalRows:", "stats.returnedRows:", "stats.userAllowedRows:"}),
    #"Expanded results" = Table.ExpandListColumn(#"Expanded stats", "results"),
    #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"report_date", "is_correction", "narrative", "report_title", "slug_name", "slug_id", "office_name", "office_code", "office_city", "office_state", "market_location_name", "market_location_city", "market_location_state", "market_type", "market_type_category", "published_date"}, {"report_date", "is_correction", "narrative", "report_title", "slug_name", "slug_id", "office_name", "office_code", "office_city", "office_state", "market_location_name", "market_location_city", "market_location_state", "market_type", "market_type_category", "published_date"})
in
    #"Expanded results1"

Pat

[Anonymous]

Thanks - but it still doesn't appear that the query is fully expanded: 

Thanks for the help!