Forum Discussion

vwiles84's avatar
vwiles84
Helper III
6 years ago

Rest API - 90 Day restriction

I have access and connected to a rest API to pull in some sales data. The api is restricted to 90 days worth of data.  My question is, how do I configure Power BI to pull in current data while archiving the historical data? OR if that's not possible, how do I configure the data source of the API to adjust the data call by one day?  

 

The call lanuage is: https://website/order_items?time_start=2020-01-20T21:30:13.247Z&time_end=2020-04-15T21:30:13.247Z.  In order to keep the current data coming in, I would have to manually adjust the start time and end time. I would like to have this automated, while keeping a track of the previous days data. 

Tips and Tricks

18 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi vwiles84 

     

    You can create a table in Power Query with multiple URL strings as rows containing 90 windows and then call all and app and into one dataset

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      vwiles84 this M code will create start and end date range and will update daily, just add another column and make api call from the WebURL column and you are good to go.

      let
    MaxDays = 90,
    EndDate = Date.From(DateTime.LocalNow()),
    StartDate = Date.AddDays(Date.From(EndDate),1-MaxDays),
    Source = #table({"StartDate","EndDate"} ,{{StartDate,EndDate}}),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}, {"EndDate", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "WebURL", each "https://website/order_items?time_start=" & 
DateTime.ToText([StartDate],"yyyy-MM-dd")&"&time_end="&DateTime.ToText([EndDate], "yyyy-MM-dd"), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WebData", each Web.Contents([WebURL]))
in
    #"Added Custom1"
      • vwiles84's avatar
        vwiles84
        Helper III
        6 years ago

        Thank you! I will try that and see how it works! 

    • vwiles84's avatar
      vwiles84
      Helper III
      6 years ago

      What is the best way to call multiple URL strings? I tried editing my Json.documeent(Web.contents(www.apiurl parameter/) with an AND command but it doesn't work.  Is there another place to join the api calls?