Forum Discussion
Dynamic date in web source
Memorable MemberThis is Power BI so anything is possible :smileyhappy:
It's a bit hard to read the timeRange value but it looks like 2 dates and times, e.g. Unencoded it would read:
01/01/2016 00:00~04/01/2016 24:00
You'll need to come up with a source for those 2 values. Is it data driven? A rule based on today? Converted to UTC or a specific timezone? MM/DD/YYYY format or DD/MM/YYYY format?
Once you have that figured out you can Edit the Query and change the generated URI to an expression that contatenates strings e.g.
"https://api.enterprise.apigee.com...timeRange="&[Start Date Time]&"~"&[End Date Time]&"&timeUnit=day"
How you pass them in depends on the source. If they are pure expressions then you could just embed them. If they are specific cells in the result of another Query (my preference, for debugging) then you can use a List function to reference them, e.g.
List.First(#"Table1"[Start Date Time])
Thanks Mike you are correct in the date format. I actually was able to use several functions to get it to work in desktop but that caused another probelm. So in the advanced editor i changed the source hard coded dates to the following:
timeRange=" & Text.PadStart(Text.From(Date.Month(Date.AddMonths(DateTime.LocalNow(), -6))),2,"0") & "%2F" & Text.PadStart(Text.From(Date.Day(Date.AddMonths(DateTime.LocalNow(), -6))),2,"0") & "%2F" & Text.From(Date.Year(Date.AddMonths(DateTime.LocalNow(), -6))) & "%2000%3A00~" & Text.PadStart(Text.From(Date.Month(DateTime.LocalNow())),2,"0") & "%2F" & Text.PadStart(Text.From(Date.Day(DateTime.LocalNow())),2,"0")
That looks like a hot mess but the short of it is it limits the start date to 6 months back and the end date to today. It works beautifully in desktop but when I uploaded it to PowerBi to show off the auto refresh, the refresh fails. This is disappointing as I was hoping to be able to schedule a daily refresh on this.
Is anyone aware of a work around that I can use to have a dynamic date in the string and have refresh work?