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?