Dynamic date in web source

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 :smileywink:

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