Forum Discussion
Helper IIncremental Refresh with API
Hi PedroModa,
Power BI won’t let your report refresh online if your Web.Contents uses a URL that’s built using text joined together while the report is running. That’s because Power BI doesn’t know in advance what websites or addresses it might try to access.
To fix this, you should separate the main part of the URL (the base) from the parts that change (like dates or filters). You can do that by using the Query and RelativePath options in Web.Contents, like below.
(DataInicio as text, DataFim as text) => let Fonte = Json.Document( Web.Contents( "https://api.example.com", // Static base URL [ RelativePath = "endpoint", // Static relative path Query = [ DATAINICIO_D = DataInicio, DATAFINAL_D = DataFim], Headers = [ #"Authorization" = "Bearer YOUR_TOKEN" // Or whatever is required ]])) in Fonte
Super UserHi PedroModa ,
Yes, your suspicion is correct. The slower refresh time is almost certainly caused by the overhead of making many small API calls instead of one large one. Your original refresh made roughly 8 large calls, while your new incremental method makes around 240 small calls (8 tables * 30 days). Each of these calls has a fixed overhead for connection, authentication, and network latency. Multiplying this overhead 240 times is creating a new bottleneck that is slower than downloading the larger dataset in a few calls.
This behavior stems directly from your Power Query logic. The combination of your SQL calendar table and this specific step forces a sequential loop:
#"Invoked Custom Function" = Table.AddColumn(
#"Filtered Rows",
"fx_fPartida",
each fx_fPartida([DateText])
)This code iterates through every day in your refresh period and runs your custom function, which makes a separate Web.Contents call for each individual day. While you achieved query folding on the SQL side to get the list of dates, that folding stops there. The subsequent API calls are executed one by one, which is highly inefficient.
Your isolated test file was faster because it didn't have to compete for resources with the other 52 tables in your full report. To fix the issue in the main report, you must reduce the number of API calls. The best way to do this is to make a single API call that fetches all the data for the entire incremental period, from RangeStart to RangeEnd.
The ideal solution is to have the API endpoint modified to accept a start and end date. This would eliminate the need for the SQL calendar and the looping function. Your entire query for each table could be simplified to this:
let
// Convert parameters to text in the required format
StartDateText = DateTime.ToText(RangeStart, "yyyy-MM-dd"),
EndDateText = DateTime.ToText(RangeEnd, "yyyy-MM-dd"),
Source = Json.Document(
Web.Contents(
"URLDAMINHAAPI",
[
RelativePath = "RELATIVEPATHDAMINHAAPI",
Query = [
DATAINICIO_D = StartDateText,
DATAFINAL_D = EndDateText
]
]
)
),
// ... rest of your transformations
in
Source
To answer your final questions: Yes, this slowdown is expected with your current implementation that makes a call per day. And yes, incremental refresh is absolutely worth it with APIs, but only if the API is designed to support it by accepting date range parameters in a single query. Your experience perfectly illustrates the performance penalty when the API cannot handle this efficiently.
Best regards,