Put a variable into JSON request for Webs.Content

[Anonymous]

Hello!

Currently I want to pull JSON file from a public open database API, like this:

Sourcekeys = Json.Document(Web.Contents("https://xxxxx.xxxx.xx/yyyyy/api/v1/zz/abcde/2021/opqrstu"))

However, this open database will be updated each year with same link except change in year.

How do I replace the year 2021 as Date.Year(Date.LocalTime) or something like that?

Thank you before hand 🙂 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

You can try:

let
    startDate = Date.StartOfYear( Date.From( DateTime.LocalNow() ) ) ,
    endDate = Date.EndOfYear( Date.From( DateTime.LocalNow() ) ),
    endDateText = Date.ToText( endDate, "yyyy-MM-ddT00:00:00Z" ),
    startDateText = Date.ToText( startDate, "yyyy-MM-ddT00:00:00Z" ),
    Custom1 = "https://openapi.ariba.com/api/analytics-reporting-details/v1/prod/views/templatename?realm=myrealm&filters={""createdDateFrom"":"& startDateText &",""createdDateTo"":"&endDateText&"}"
in
    Custom1

Check the similar thread below:

https://community.powerbi.com/t5/Power-Query/Rest-API-Dynamic-Date-Query/td-p/956246

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Vera_33]

Hi @Anonymous 

You can add something like

    thisYear = Date.Year(DateTime.LocalNow()),
    url = "https://xxxxx.xxxx.xx/yyyyy/api/v1/zz/abcde/"&Text.From(thisYear)&"/opqrstu",
    Sourcekeys = Json.Document(Web.Contents(url))