Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Put a variable into JSON request for Webs.Content

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 🙂 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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))
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!