Changing Source URL with current date

[i0054524]

Hello All,

I'm running into a problem I'm hoping someone can help with.  I'm fairly new to PowerBI.  Here is what I'm trying to do.

I'm pulling data from a website using the web as a source connector in PowerBI.  An example of the url for simplicity is (madeup url): data.company9.com/scripts/events.asp?month=04&day=28&year=2019&stype=17&type=B

In the url, the only items I want to change are in Bold.  Baiscally, I want the numbers to change in the url based on the current date when the data is refreshed.  Therefore, if this was ran on May 1, 2019, month would equal 05, the day would equal 01, and the year would be 2019 (the new url would be: data.company9.com/scripts/events.asp?month=05&day=01&year=2019&stype=17&type=B    Everything not bolded in the url would stay the same.  I believe either going to the advanced editor and using Power Query could work....or maybe parameters.  Not sure as I haven't had any luck.  Can anyone provide some guidance on this.

Thank you! 

[TomMartens]

Hey,

if you create a new Blank Query in the Query Editor of Power BI and insert the following code snippet, you will receive a string that contains the current date:

let
    today = DateTime.LocalNow(),
    todayDay = Text.PadStart(Text.From(Date.Day(today)),2,"0"),
    todayMonth =  Text.PadStart(Text.From(Date.Month(today)),2,"0"),
    todayYear = Text.From(Date.Year(today)),
    url = Text.Combine({"data.company9.com/scripts/events.asp?month=", todayMonth, "&day=" , todayDay, "&year=", todayYear, "&stype=17&type=B"}, "")
in
    url

Hopefully this is what you are looking for.

Regards,

Tom

[i0054524]

Thank you!  That worked for creating a string with the current date in the format I want in the url.  Now, I might be thinking about this too hard, but now that I have that string....how do I pass that string into the query so that the url gets created and the data is pulled from that url?  Not sure if that makes sense or not.