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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 44 members 1,029 guests
Please welcome our newest community members: