API Dynamic Date Query

[karissashapard]

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:

https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=&start_time=2018-01-31&end_tim...

Thank you

[v-shex-msft · Accepted 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&quote=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&quote=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

[Jeefr]

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.

[BloodSteyn]

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.