Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |