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,
I am looking to pass through yesterday's date through an API request as a data source in Power BI. I cant figure out how to pass yesterday's date as a parameter for the API. The link looks like this:
Thank you
Solved! Go to Solution.
Hi @karissashapard,
You can modify bold part with 'DateTime.LocalNow' function to achieve dynamic date filter.
https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=8d83618d94fdb60a6Vn2356d&start_time=2018-01-31&end_time=2018-02-28&base=USD"e=CAD&fields=averages
Sample:
let Source = Csv.Document(Web.Contents("https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=8d83618d94fdb60a6Vn2356d&start_time=2018-01-31&end_time="&Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"yyyy-MM-dd")&"&base=USD"e=CAD&fields=averages"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"base_currency", type text}, {"quote_currency", type text}, {"start_time", type datetime}, {"open_time", type datetime}, {"close_time", type datetime}, {"average_bid", type number}, {"average_ask", type number}, {"average_midpoint", type number}}) in #"Changed Type"
Regards,
Xiaoxin Sheng
Hi @karissashapard,
You can modify bold part with 'DateTime.LocalNow' function to achieve dynamic date filter.
https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=8d83618d94fdb60a6Vn2356d&start_time=2018-01-31&end_time=2018-02-28&base=USD"e=CAD&fields=averages
Sample:
let Source = Csv.Document(Web.Contents("https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=8d83618d94fdb60a6Vn2356d&start_time=2018-01-31&end_time="&Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"yyyy-MM-dd")&"&base=USD"e=CAD&fields=averages"),[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"base_currency", type text}, {"quote_currency", type text}, {"start_time", type datetime}, {"open_time", type datetime}, {"close_time", type datetime}, {"average_bid", type number}, {"average_ask", type number}, {"average_midpoint", type number}}) in #"Changed Type"
Regards,
Xiaoxin Sheng
thanks for this solution, i'v been looking far and wide for this.
Do you know how this can be done, so it's able to update on power-bi online service?
its says that it can't update dynamic queries.
Thank you kindly for this.
I've now used this solution to set up a dynamic url with a parameter to set the date and pull the latest (yesterday) csv with the Corona Virus global cases.
Query:
let
Source = Csv.Document(Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/" & Date & ".csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Province/State", type text}, {"Country/Region", type text}, {"Last Update", type datetime}, {"Confirmed", Int64.Type}, {"Deaths", Int64.Type}, {"Recovered", Int64.Type}})
in
#"Changed Type"
Using the fucntion you provided as the parameter:
Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"MM-dd-yyyy") meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
Still learning, and this community with people like yourself is an indispensible resource.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |