Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎08-14-2018
Accepted Solution

API Dynamic Date Query

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


Accepted Solutions
Community Support Team
Posts: 7,456
Registered: ‎08-14-2016

Re: API Dynamic Date Query

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post


All Replies
Community Support Team
Posts: 7,456
Registered: ‎08-14-2016

Re: API Dynamic Date Query

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |