Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LarryBroward
Frequent Visitor

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

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

 

 

 

6 REPLIES 6
lbendlin
Super User
Super User

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.

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. 

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

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

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.

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors