Forum Discussion

lsuneson's avatar
lsuneson
New Member
10 years ago
Solved

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...
Need Help
  • lsuneson's avatar
    lsuneson
    10 years ago

     

    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!

lsuneson's avatar
lsuneson
New Member
10 years ago

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's avatar
cwebb
Advocate V
10 years ago

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's avatar
    lsuneson
    New Member
    10 years ago

     

    Hi cwebb, thanks 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's avatar
      cwebb
      Advocate V
      10 years ago

      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's avatar
        lsuneson
        New Member
        10 years ago

        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?