cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lsuneson Frequent Visitor
Frequent Visitor

API call limit - introduce delay

Hi,

 

I'm using power bi desktop to connect to web services using the from web query. Everything works well before i try to merge queries. I web service I connect to only allows 1 call per 2 second so I think i hit that limitation. Is there a way to introduce a delay to get around this? 

 

Ludvig

1 ACCEPTED SOLUTION

Accepted Solutions
lsuneson Frequent Visitor
Frequent Visitor

Re: API call limit - introduce delay

 

Thanks, downloaded Fiddler and it looks like power bi makes a new http call for more or less every operation. Have only taken a couple of programming classes 10 years ago but that would not even been aloud in 101 classes!  

 

I have tried Table.Buffer but it didn't help much in my more complex queries. I guess I will try to keep the queries to raw data and do all calucaltions etc using the modelling functionality and see if that helpes. 

 

Thanks again Chris!

View solution in original post

8 REPLIES 8
konstantinos Senior Member
Senior Member

Re: API call limit - introduce delay

@lsuneson No expert in API calls but this might help you - also has a reference to an old similar post

https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

Konstantinos Ioannou
lsuneson Frequent Visitor
Frequent Visitor

Re: API call limit - introduce delay

thanks @konstantinos. I have actually seen that post but I couldn't figure out how to use Function.InvokeAfter(). 

Below is simple exampel of my query, if anyone can give some advice, it woudl be very appriciated. 

 

let
   Source = Json.Document(Web.Contents("https://api.skulabs.com/purchase_order/get?limit=100&skip=&sort=")),
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "distributor_id"},          {"Column1.number", "Column1.distributor_id"}),
   #"Merged Queries" = Table.NestedJoin(#"Expanded Column1",{"Column1.distributor_id"},MapTable,    {"Column1._id"},"NewColumn",JoinKind.LeftOuter)
in
#"Merged Queries"

cwebb
Advisor

Re: API call limit - introduce delay

Try something like this:

 

let
   Source = ()=> Json.Document(Web.Contents("https://api.skulabs.com/purchase_order/get?limit=100&skip=&sort=")),
   #"Converted to Table" = Table.FromList(Function.InvokeAfter(Source, #duration(0,0,0,2)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "distributor_id"},          {"Column1.number", "Column1.distributor_id"}),
   #"Merged Queries" = Table.NestedJoin(#"Expanded Column1",{"Column1.distributor_id"},MapTable,    {"Column1._id"},"NewColumn",JoinKind.LeftOuter)
in
#"Merged Queries"

This adds a two second delay before calling the web service; the length of the delay is determined by #duration(0,0,0,2) in the "Converted to Table" step.

 

HTH,

 

Chris

lsuneson Frequent Visitor
Frequent Visitor

Re: API call limit - introduce delay

 

Hi @cwebbthanks a lot for your help, it's very appreciated. 

I think source is of type list and InvoiceAfter needs a function as parameter because I'm getting the following error:

 

Expression.Error: We cannot convert a value of type List to type Function.

Details:
Value=List
Type=Type

 

Any advice on how to fix this?

cwebb
Advisor

Re: API call limit - introduce delay

I also made a change to the Source step in my example that means it should return a function, so maybe something else is going on here. Can you post the code that gives you this error?

 

Thanks,

 

Chris

lsuneson Frequent Visitor
Frequent Visitor

Re: API call limit - introduce delay

Sorry, I missed that. It works well now, thank you so much!

 

Is it away to trace the calls being made? I seem to have a loop over the http get call as this is now very slow.. 

Are merge or any other functions causing loops/iterations over the calls?

 

cwebb
Advisor

Re: API call limit - introduce delay

There's no easy way of tracing this in Power BI; you could use a tool like Fiddler though. 

 

It is possible that multiple calls are being made. It might be worth experimenting with Table.Buffer() here (see https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buff... for some background). Maybe something like this would work:

 

let
   Source = ()=> Json.Document(Web.Contents("https://api.skulabs.com/purchase_order/get?limit=100&skip=&sort=")),
   #"Converted to Table" = Table.FromList(Function.InvokeAfter(Source, #duration(0,0,0,2)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.Buffer(Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "distributor_id"},          {"Column1.number", "Column1.distributor_id"})),
   #"Merged Queries" = Table.NestedJoin(#"Expanded Column1",{"Column1.distributor_id"},MapTable,    {"Column1._id"},"NewColumn",JoinKind.LeftOuter)
in
#"Merged Queries"

Chris

lsuneson Frequent Visitor
Frequent Visitor

Re: API call limit - introduce delay

 

Thanks, downloaded Fiddler and it looks like power bi makes a new http call for more or less every operation. Have only taken a couple of programming classes 10 years ago but that would not even been aloud in 101 classes!  

 

I have tried Table.Buffer but it didn't help much in my more complex queries. I guess I will try to keep the queries to raw data and do all calucaltions etc using the modelling functionality and see if that helpes. 

 

Thanks again Chris!

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)