Dynamic date in web source

Question

I currently have several connections that make calls out to Apigee to grab statistical data and visualize it in power bi.

The call uses a date range and has a format that looks like this:

https://api.enterprise.apigee.com...timeRange=01%2F01%2F2016%2000%3A00~04%2F01%2F2016%2024%3A00&timeUnit=day

the format passed in is month, day and year. Right now i have it hardcoded but i was wondering if there is a way to make the date dynamic so that i could do something like a rolling 6 months rather than having to change the date every time i want to update the data.

Is this possible?

captainhenkka · Answer

I had this same issue, ie how to build a web-json query so that it always gets today's date in it in the BOLD-part:&nbsp;http://my.api.com/v1/wave/2018-04-18T04:30:00Z/2018-04-23T23:59:59Z?api_key=myapikey&nbsp;I must say this was a bit of a headache, but lastly I figured this our based on a lot of sources. Mind you that I am a Captain, so not much knowledge about coding&nbsp;:smileywink:&nbsp;The solution was like this.&nbsp;1. Create a blank query in Power BI Query Editor (or edit an existing one)2. Problem 1 is how do we get today's date?-- Solution -&gt; we use the M function&nbsp;DateTime.LocalNow(),&nbsp;which returns the current local date an time and looks something like this&nbsp;2018-04-23T21:24:58.0134360. Note! This is now formatted as a datetime and not a piece of text (ie string). BTW, there are a multitude of other datetime functions which you might find useful.&nbsp;3. Problem 2 is how do we get this to work in the URL?-- Solution -&gt; we convert it to text with the&nbsp;DateTime.ToText function. Now the DateTime.ToText(DateTime.LocalNow()) will return the actual string of data and looks like this&nbsp;23/04/2018 21:27:13. Notice how the format changes. But our URL cannot work with this format as it needs it to be&nbsp;2018-04-23.&nbsp;So we need to format the date.&nbsp;4. Problem 3 is how do we format this piece of text?&nbsp;-- Solution -&gt; we use the formating options&nbsp;of the DateTime.ToText function by typing it up like this:&nbsp;DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd") which now gives us a piece of text and reads exactly&nbsp;2018-04-23&nbsp;5. Last issue is how to use this in a nice way in our Power BI query editor and get the URL to be dynamic?-- Solution -&gt; we define a parameter called Date (could be anything, naturally) and populate this parameter with the Current DateTime, which we also format to text. The end result looks like this, after we add in the Date parameter into the URL. Note! you need to split your text with " &amp; [your parameter here] &amp;" to get this to work.&nbsp;&nbsp;&nbsp;let
    // first we create a parameter called Date and populate it with the current datetime in textformat    Date = DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd"),
    // then we add in the parameter into our URL and in between a few symbols " &amp; Date &amp;"    Source = Json.Document(Web.Contents("http://www.apie.com/v1/wave/2018-04-18T04:30:00Z/" &amp; Date &amp;"T23:59:59Z?api_key=1231434")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"time", "WaveHS", "WTP", "ModalWDi"}, {"Column1.time", "Column1.WaveHS", "Column1.WTP", "Column1.ModalWDi"})
in
    #"Expanded Column1"&nbsp;&nbsp;&nbsp;

mike_honey · Answer

This 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])

anonymous · Answer

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?

curth · Answer

To make this work, you have to split the query parameters from the base URL by putting them into the options record. Change the formula from something like this

=Web.Contents("https://some/site?parameter1=" & value1 & "&parameter2=" & value2)

To something like this

=Web.Contents("https://some/site", [Query=[parameter1=value1, parameter2=value2]])

value1 and value2 have to be text values and will be properly escaped.

anonymous · Answer

Ok, so i modified the query to use the options like such:&nbsp;"https://api.enterprise.apigee.com/v1/organizations/XXX/environments/prod/stats/apis",[Query=[select="sum(message_count),sum(is_error),sum(total_response_time)",timeRange="03/01/2016 00:00~04/01/2016 24:00"]]&nbsp;This works in Power BI desktop but then when I upload it, I get a different error than my previous one. &nbsp;First it tells me to update the credentials for the data source. &nbsp;It uses Basic Auth so I put in my credentials and click ok and then i receive the following error:&nbsp;"Failed to update data source credentials: We are unable to connect to the data source. Please ensure that data source is accessible."&nbsp;When I expand the details I see that there is a 400 bad request. &nbsp;Again though, this works in Desktop with basic authorization but in PowerBI.com I'm getting an error.&nbsp;Activity ID:959576be-64b2-4ad1-a6f7-0158e62b4509Request ID:1a20c004-1960-f6c1-deab-be51cf066a93Status code:400Time:Wed Apr 13 2016 11:31:10 GMT-0400 (Eastern Daylight Time)Version:13.0.1100.319Cluster URI:https://wabi-us-north-central-redirect.analysis.windows.netDetails:Web.Contents failed to get contents from 'https://api.enterprise.apigee.com/v1/organizations/XXX/environments/prod/stats/apis' (400): Bad Request

Forum Discussion

Dynamic date in web source

8 Replies

Related content

Dynamic Date Offset Navigator

Convert Dynamic Data source

Date Range Slicer between Dynamic Dates

Dynamic Source path

Dynamic Date Sicer