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 looks like this:

https://web-services.oanda.com/rates/api/v2/rates/candles.csv?api_key=&start_time=2018-01-31&end_time=2018-02-28&base=USD&quote=CAD&fields=averages

 

Thank you

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

3 Replies

  • 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.