cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Conjured
New Member

Power query makes extra API calls

Hello! 

 

I have made an API GET request in Excel PQ and ran into a problem.  


If I make a new API GET request of data then PQ makes 2 API calls instead of 1. However, after I have received the data, if I simply refresh the query again (repeat exactly the same query), then PQ makes 1 API call. So, it doubles the number of any new API calls. 

 

I turned off background refresh, used "buffer" functions, but it's not working. 

As suggested in the article below, I played around with query delays, but there is not much of a difference. 

 

https://social.technet.microsoft.com/Forums/exchange/en-US/edc8ab50-8b22-4383-b2fb-6ffc600d957f/odd-...

 

Please help, I am stuck.

 

 

 

 

Query 1 simply requests page numbers

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, 3, 0), IsRetry=false]), #duration(0, 0, 0, 5)),
Buffered = Binary.Buffer(Call),
Json = Json.Document(Buffered),
Pages = Json[PagesCount],
ListPages = List.Numbers(1,Pages,1),
ToTable = Table.FromList(ListPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in
ToTable



Query 2 runs almost the same query but with pages parameter and with loop

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, 0, 5), IsRetry = false]),
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,5)) else
if Record.FieldNames(Buffered){0} = "error" = true or Record.Field(try Buffered, "HasError") = true or TestError(Binary.Buffer(MyCall())) <> 200 = true
then Function.InvokeAfter(() => @ FxRetry (MyCall), #duration(0,0,0,20)) else null
in Output,

Запрос = let
Source = FxRetry(Call)
in Source
in Запрос


Query 3 = applies Query 1 and Query 2 to table of parameters. Its not that important, I think.

 

 

 

1 REPLY 1
v-kelly-msft
Community Support
Community Support

Hi  @Conjured ,

 

There is another method described here: Chris Webb's BI Blog: Speed Up Data Refresh Performance In Power BI Desktop Using Table.View Chris W...
that might be more reliable.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors