Need help with relative path and query

[Marie-Eve]

Hello,

I need help building my source in Power Query to link to this site https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2498?q=report_date=6/19/2024&allSections=tru... and be able to have it update automatically once online.  Like many others, I have started with this blog to build my query  https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 but I get this error message (basically the name "report_date" is not recognized)

It's like the query doesn't accept the double = with this part: q=report date= 

When I take it out, of course the url doesn't work, but if I copy it on a web page and add report_date= manually it does reach the page.  So it's basically just the syntax that is problematic.  

I found this document that explain the logic of the site URL https://mpr.datamart.ams.usda.gov/LMPRS-API-User-Guide.pdf but I can't get passed that glitch.  I've tried for a few days now to write the query or to find if anybody had this problem but couldn't find anything.  Hopefully someone here can help 🙂

[Marie-Eve]

Thanks a lot!  I should have asked sooner 😄

[WanderingBI]

I think it is working with below code, you can use it either with the full url or with the relative part and the query.

Maybe the reason why you had trouble is that the API blocks you out when you do too many requests. After waiting a few minutes it will work again.

let
    // Source = Json.Document(Web.Contents("https://mpr.datamart.ams.usda.gov/services/v1.1/reports/2466/Summary?q=report_date=08/05/2019"), 65001),
    pathBase = "https://mpr.datamart.ams.usda.gov/services/v1.1/",
    pathRelative = "reports/2466/Summary?",
    pathQuery = "q=report_date=08/05/2019",
    pathFull = pathBase & pathRelative & pathQuery,
    Source = Json.Document(Web.Contents(pathFull), 65001),
    
    yesterdayDate = DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyy-MM-dd"),
    queryWithTodayDate = "report_date=" & yesterdayDate,
    SourceRelative = Json.Document(
        Web.Contents(pathBase, [
            RelativePath = pathRelative,
            Query = [q = queryWithTodayDate]
        ])
    )
in
    Source

[Marie-Eve]

Hello, I was able to recreate this with the the url in my original request and tried to replicate this with prior 2 days but it's not working. 

I duplicated the request and change this part to -2 but it still showed me the data of -1

yesterdayDate = DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyy-MM-dd"),