Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rossnruthie
Resolver I
Resolver I

Dynamic date in web source

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?

8 REPLIES 8
mike_honey
Memorable Member
Memorable Member

This is Power BI so anything is possible Smiley Happy

 

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

Can you provide an example of this?

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?

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.

Ok, so i modified the query to use the options like such:

 

"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"]]

 

This works in Power BI desktop but then when I upload it, I get a different error than my previous one.  First it tells me to update the credentials for the data source.  It uses Basic Auth so I put in my credentials and click ok and then i receive the following error:

 

"Failed to update data source credentials: We are unable to connect to the data source. Please ensure that data source is accessible."

 

When I expand the details I see that there is a 400 bad request.  Again though, this works in Desktop with basic authorization but in PowerBI.com I'm getting an error.

 

Activity ID:959576be-64b2-4ad1-a6f7-0158e62b4509
Request ID:1a20c004-1960-f6c1-deab-be51cf066a93
Status code:400
Time:Wed Apr 13 2016 11:31:10 GMT-0400 (Eastern Daylight Time)
Version:13.0.1100.319
Cluster URI:https://wabi-us-north-central-redirect.analysis.windows.net
Details:Web.Contents failed to get contents from 'https://api.enterprise.apigee.com/v1/organizations/XXX/environments/prod/stats/apis' (400): Bad Request

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:

 

http://my.api.com/v1/wave/2018-04-18T04:30:00Z/2018-04-23T23:59:59Z?api_key=myapikey

 

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 Smiley Wink

 

The solution was like this. 

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 -> we use the M function DateTime.LocalNow(), which returns the current local date an time and looks something like this 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.

 

3. Problem 2 is how do we get this to work in the URL?

-- Solution -> we convert it to text with the DateTime.ToText function. Now the DateTime.ToText(DateTime.LocalNow()) will return the actual string of data and looks like this 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 2018-04-23. So we need to format the date.

 

4. Problem 3 is how do we format this piece of text? 

-- Solution -> we use the formating options of the DateTime.ToText function by typing it up like this: DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd") which now gives us a piece of text and reads exactly 2018-04-23

 

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 -> 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 " & [your parameter here] &" to get this to work. 

 

 

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 " & Date &"
Source = Json.Document(Web.Contents("http://www.apie.com/v1/wave/2018-04-18T04:30:00Z/" & Date &"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"

 

 

 

Thanks a lot, that worked for me nicely 🙏 

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:

 

http://my.api.com/v1/wave/2018-04-18T04:30:00Z/2018-04-23T23:59:59Z?api_key=myapikey

 

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 Smiley Wink

 

The solution was like this. 

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 -> we use the M function DateTime.LocalNow(), which returns the current local date an time and looks something like this 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.

 

3. Problem 2 is how do we get this to work in the URL?

-- Solution -> we convert it to text with the DateTime.ToText function. Now the DateTime.ToText(DateTime.LocalNow()) will return the actual string of data and looks like this 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 2018-04-23. So we need to format the date.

 

4. Problem 3 is how do we format this piece of text? 

-- Solution -> we use the formating options of the DateTime.ToText function by typing it up like this: DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd") which now gives us a piece of text and reads exactly 2018-04-23

 

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 -> 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 " & [your parameter here] &" to get this to work. 

 

 

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 " & Date &"
Source = Json.Document(Web.Contents("http://www.apie.com/v1/wave/2018-04-18T04:30:00Z/" & Date &"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"

 

Ps. you could also build this same stuff by using a simpler Date

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors