Automatically updated date in the connection to data source (web)

[BerlinAlexander]

Hi everyone. I want to use an api connection to get some data. I tried to use the simple "Get Data" -> "Web" method and it worked fine. I then tried to use the Power Query advanced editor and it also works. The code is:

= Json.Document(Web.Contents("[WebpageAdress]/api/sale/by-period?date-from=2020-01-01&date-to=2020-01-29"))

The problem is I would like the link to update automatically when I refresh the report. As you see, there's info on the start date and end date in the line quoted above. I would like these dates to update authomatically. Otherwise I would get data for the same period repeatedly, which is pointless. I would like to always have:

Today minus 29 days as the start date ("date-from")
Today minus 1 day as the end date ("date-to")

I've been trying for find a solution for hours with no luck. Any ideas?

[v-juanli-msft]

Hi @BerlinAlexander 

First you need to follow lbendlin's suggestion to put "start date" and "end date" outside the base url and in a query field.

Then to refresh your data from the url with updated date as you like, please create parameter for "start date" and "end date" in Powre query and use them in your query above.

After publish to Power BI Service, you could go to dataset->Setting->chnage parameters.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-parameters

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Web.Contents accepts query parameters. You can use these to provide the date range or any other URL parameters.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...