Call API 1 day at a time and create/append the data until end date is satisfied. Add 60 sec delay

[LarryBroward]

The requirement is to call an API and append to the output api 1 day at a time and add a 60 second delay in between. This would be refeshed daily. It would be the last N number of days, two weeks max .So I will pass a start and end date. The basic functionality is working, I can get one day of data returned. The problem is incrementng and getting the next days data. Also the delay function (Just Getting a date with a duration) isnt working so i am not sure if that is allowed or if I am notdoing right. I do know the URL isn't firing a second time because the API would fail without the delay. I think I am on the right track. Let me know if you have any suggestions or if I am way off base.

Thanks

(ApiParamStartDate as date,ApiParamEndDate as date,optional loop as number, optional data as list) =>

let

sd = Date.ToText(ApiParamStartDate) & "%2000%3A00", //12 AM One day at a time , loop back and get the next day after incrementing.
ed = Date.ToText(ApiParamStartDate) & "%2023%3A59", // 11:59 PM

Source = Json.Document(Web.Contents(
"https://XXXXXXXXXXX.com/api/v4/get_readings/?device_sn=xxxxx&start_date=" &Text.From(sd)&"&end_date="&Text.From(ed)&"&output_format=json&page_num=1&per_page=2000",
[Headers=[Accept="application/json", Authorization="Token xxxxxxxxxxxxxxxxxxxxx"]])),

currentData = Source, // 1st api data returned

appendedData = Source,
if currentData is null and data is null then {}
else if data is null then List.Combine({{}, currentData})
else if data is null then List.Combine ({data,{}})
else List.Combine({data,currentData}),

//loop
loopNum = loop + 1,
output =
if ApiParamStartDate <= ApiParamEndDate or loopNum < 10 // Get 10 daysof day 1 api call returns 1 day
// delay() call a 60 second delay function here
@#"GET Activity" (Date.AddDays(ApiParamStartDate,1),"",loopNum, appendedData) // add 1 day to start date paramater, End date will be the same
else
//
in output

[lbendlin]

That's not how delays work, unfortunately.  Please read about Function.InvokeAfter  . Please note that this is a rather advanced topic, so expect slow going.

Please also thoroughly read the documentation for Web.Contents - PowerQuery M | Microsoft Learn and use the Query parameter.

[LarryBroward]

Sorry for the confusion. This post is about the loop not working. The 61 sec delay is commented out and just is a place holder that I will tackle once the loop is working. 

[lbendlin]

please read both documentations. Refactor your code accordingly. Then we can work on the pagination, and on the delay.

[LarryBroward]

Good morning,

I actually thought you were replying to my most recent post where I simplifed it and got it as close as I could and from what I can see my loop is not iterating otherwise I would get a  429 too mant request error.  I can not get it to go to the next day. Once the loop is working I was going to go back and address the 60 second delay between api calls. This seems like a pretty common requirement, I worked through a bunch of youtube videos on looping but their a bit different, so I am reaching out to the community which I only do on ocassion when I am stuck.

Thanks for your help!

https://community.fabric.microsoft.com/t5/Power-Query/API-Loop-not-working-in-M-Query/m-p/3452514#M1... 

[lbendlin]

I still don't see you applying the documented principles (the Query parameter being the most important one).  Happy to help you but you need to bring some effort to this too.

[LarryBroward]

Thanks for replying. I gave it my best shot.  This part of power bi I find to be very obscure that is why I usually write a .net console app to handle the complexity. I thought I might be able to do the same thing in Power BI instead of writing an application but I wasn't expecting  this. Each one teach one.