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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Conjured
New Member

Power Query makes extra API calls

Hello!

I have faced a certain problem when making get API requests with PQ.  

 

In some cases, PQ makes extra calls. For example, if I make a new API GET request of data then PQ can make 2 API calls instead of 1. However, after I have received the data, if I repeat exactly the same query (refresh the same query), then PQ makes 1 API call.

 

It seems that the reason might be on load time. It has been stated so in the article here. However, I don't know how to fix it.

 

Please help, I am stuck.

 

 

Query 1 retrieves all numbers of pages. Query 2 makes the same as Query 1, but with page numbers as parameters and it has a loop. Query 3 (not listed here) invokes functions Query 1 and Query 2 based on the parameters of table in Query 3.

Query 1 =

(INN as text, DatesFrom, DatesTo) =>
let
    Call = Function.InvokeAfter(()=> Web.Contents("https://parser-api.com/parser/arbitr_api/run.php?", [Query = [Inn = INN, key = API, search = "1", DateFrom = DatesFrom, DateTo = DatesTo], Timeout=#duration(0, 0, 5, 0)]), #duration(0, 0, 0, 5)),
    Buffered = Binary.Buffer(Call),
    Json = Json.Document(Buffered),
    Pages = Json[PagesCount],
    ListPages = List.Numbers(1,Pages,1),
    Transformed= Table.FromList(ListPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    
in
    Transformed

Query 2 =

(Pages, INN, DatesFrom, DatesTo) =>
let
    Call = () => Web.Contents("https://parser-api.com/parser/arbitr_api/run.php?", [Query = [Inn = INN, key = API, search = "1", page = Pages, DateFrom = DatesFrom, DateTo = DatesTo], Timeout=#duration(0, 0, 5, 0)]),
    FxRetry = (MyCall as function) => let Buffered = Json.Document( Binary.Buffer (MyCall())), 
                   Output = 
                   if Record.FieldNames(Buffered){0} = "error" = false 
                   then Function.InvokeAfter(() => Buffered, #duration(0,0,0,20)) else
                   if Record.FieldNames(Buffered){0} = "error" = true or Record.Field(try Buffered, "HasError") = true 
                   then Function.InvokeAfter(() => @ FxRetry (MyCall), #duration(0,0,0,20)) else null
                   in Output,
                                      
     Query = let
             Source = FxRetry(Call)
             in Source
    in Query

 

1 REPLY 1
lbendlin
Super User
Super User

this would be an excellent question to ask Chris Webb

 

https://blog.crossjoin.co.uk/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors