Forum Discussion

karissashapard's avatar
karissashapard
Frequent Visitor
7 years ago
Solved

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 look...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    BloodSteyn
    New Member
    6 years ago

    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.