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,
My company uses service that outputs data (KPIs) via web requests, which include list of required KPIs, line numbers and required date. I can easily transform output data into proper tables, but I need to have access to at least data from last 3 days. This should be 3 separate queries and the problem is "How to refresh recent date in URL?". For example today is 05_02_2019, I need to run 2 additional queries with 05_01_2019 and 04_30_2019 as part of URL and tomorrow I will need to run queries with 05_03_2019, 05_02_2019, 05_01_2019 and so on. Is there a way to automatically refresh this part of URL every day for each query?
Also there is a option to input "Today" value instead of date in URL and I thought about running a query every day and storing all the results in some excel table, but if there is a way to keep this process in Power Bi I would like to do so.
Solved! Go to Solution.
This is possible. But I need more inputs. Will you be able to do the following...
Once you post the query here, I will be able to modify it to automatically change the CurrentDate, Day-1, and Day-2 and post the modified query back to you.
But the approach is as follows...
Hi,
Can someone help in the below formula.
https://igateway.co.za/reports/451d723ec0etgjujbb11c3ad0dec22fb67/AllPolicies_2023-01-16.xlsx
I just want to modify this url so that the date change automatically to to Today's date and in the same date format.
Please help
Try adding these to your PowerQuery that fetches the data:
// Get the current date in the format 'YYYY-MM-DD'
currentDate = Text.From(DateTime.LocalNow(), "yyyy-MM-dd"),
// Base URL without the date part
baseUrl = "https://igateway.co.za/reports/451d723ec0etgjujbb11c3ad0dec22fb67/AllPolicies_",
// Construct the complete URL with the current date
completeUrl = baseUrl & currentDate & ".xlsx"
This is possible. But I need more inputs. Will you be able to do the following...
Once you post the query here, I will be able to modify it to automatically change the CurrentDate, Day-1, and Day-2 and post the modified query back to you.
But the approach is as follows...
Thank you, but I am not able to post the link and query here due to privacy policy. Guess I`ll need to figure it out by myself with the help of your guide. Thank you for a quick and great response.
Giving a sample power query script below...
let Text1 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(DateTime.Date(Date1),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Text2 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-1),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Text3 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-2),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Output = Text.Combine({Text1,Text2,Text3}) in Output
Try pasting this into a blank query and run it... You will get some idea.
I am trying to build an URL and use it as an API to fetch data from a thirdparty tool. I need to pass the dates as a varialbles.
Could you please let me know how to achieve this. The first date (marked in red) is 30 days earlier than later date (marked in blue)
https://aaaaaa.bbbbbbb.com/api/historicdata.json?id=NNNNN&avg=MMMMM&sdate=2020-02-21-00-00-00&edate=2020-03-21-00-00-00&usecaption=1&username=xxxxxx&password=yyyyyyy
thanks in advance
All the best then...
Posting a sample power query... Hope it will help you...
let Text1 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(DateTime.Date(Date1),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Text2 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-1),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Text3 = let Date1 = DateTime.LocalNow(), Date1Text = Date.ToText(Date.AddDays(DateTime.Date(Date1),-2),"yyyy-MM-dd"), URLPrefix = "ABC", URLSuffix = "XYZ", MyURL = Text.Combine({URLPrefix,Date1Text,URLSuffix}) in MyURL, Output = Text.Combine({Text1,Text2,Text3}) in Output
Try pasting this into a blank query and run it. You will get some idea. Subsequently, you may replace the URL in your automatically generated power query script with the variable "MyURL" in the above example.
For this example, I have used Text.Combine() function to merge the outputs of the three nested let-ins into a single one. In your real case, you will have to use Table.Combine(). Modify wherever necessary.
Also, notice the Date.AddDays function in Day-1 and Day-2 let-in statements.
All the best!
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |