Rest API - 90 Day restriction

[vwiles84]

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. 

[Mariusz]

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

[vwiles84]

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?

[parry2k]

@vwiles84 did you tried my solution, parameters in that is already part of URL

Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

[vwiles84]

I have not yet. I will now but, i think if I can just make 4 calls with the 90 day date range (01/01/20-03/31/20 then 4/1/20-6/30/20, then 7/1/20-9/30/20, etc), it will work the same. I don't know that i need to specify the max number of 90 if I can make 4 API calls with the date range changed.  If that make sense. 

[parry2k]

@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]

I can't get this to work. 

[parry2k]

@vwiles84 are you getting error or what? You have to provide more details otherwise I have no idea what should I reply. Is this url public or paid? Is there way I can test at my end?

[vwiles84]

It is no a public api, i have generated a token from the SaaS 

[vwiles84]

There is no error, it just displays all of the query in red.

[parry2k]

@vwiles84 don't know why but it works at my end. Assuming you pasted the whole code and replaced your existing code. Can you share pbix file using one drive/google drive to get your answer quickly? Remove any sensitive information before sharing it.

[vwiles84]

I started with a blank query, copied your coded and modified it to include the whole API call. 

[parry2k]

@vwiles84 so do you see the table or you are not even getting the table with all the columns before you make an api call, (before last step)

[vwiles84]

I do not see a table or columns

[parry2k]

@vwiles84 seems like  code is not copied correctly, you should be seeing this

[vwiles84]

When I copy the code in the query, it converts it to this

[vwiles84]

OK i got it to work by using the advanced editor.

[vwiles84]

But it is erroring out and not brining in the sales data. it's just showing the 2 date.  Trying to view the error locked up my query editor. So I'm not sure this is going to work. 

I will try to connect the 4 calls with the date range in 90 day intervals.  I can make multiple calls so I just have to find the best way to do that. 

Thanks for your help. 

[vwiles84]

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