Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.