cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
i0054524 Frequent Visitor
Frequent Visitor

Changing Source URL with current date

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! 

2 REPLIES 2
Super User
Super User

Re: Changing Source URL with current date

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

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
i0054524 Frequent Visitor
Frequent Visitor

Re: Changing Source URL with current date

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 1,088 guests
Please welcome our newest community members: