Forum Discussion
Incremental Refresh - Stored Procedure in SQL Server
Hi,
I am trying to find this information if we can use stored procedures & do an incremenatl refresh - I have found very conflicting views on the forum & would love to have a final answer on this.
I think in an ideal work something like below should work without any issue
let
Source = Sql.Database("your_server", "your_database"),
Data = Value.NativeQuery(
Source,
"EXEC your_stored_procedure @StartDate = p1, @EndDate = p2",
[p1 = RangeStart, p2 = RangeEnd],
[EnableFolding = true]
)
in
Data
Thanks!
Hi Max_701,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to GilbertQ, for his input on this thread.I understand there is a lot of mixed information about using stored procedures with Incremental Refresh in Power BI, so go through the below them.
Incremental refresh depends on query folding Power Query must be able to push your RangeStart / RangeEnd filters down to SQL Server. Stored procedures are treated like a “black box,” so folding often does not work reliably, even when using Value.NativeQuery. If folding breaks, Power BI will load the entire dataset and filter locally, which removes the benefit of incremental refresh.
Recommendation is to use a parameterized SQL query or a view instead of a stored procedure for incremental refresh, as this guarantees folding and predictable refresh behaviour.If you must use a stored procedure:
- Make sure it accepts @StartDate and @EndDate and applies them in the WHERE clause.
- Call it with Value.NativeQuery and [EnableFolding = true].
- Test folding in Power Query (Right-click step → View Native Query).
Kindly refer to the below mentioned documentation links for more information:
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Value.NativeQuery - PowerQuery M | Microsoft Learn
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
3 Replies
Hi Max_701,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to GilbertQ, for his input on this thread.I understand there is a lot of mixed information about using stored procedures with Incremental Refresh in Power BI, so go through the below them.
Incremental refresh depends on query folding Power Query must be able to push your RangeStart / RangeEnd filters down to SQL Server. Stored procedures are treated like a “black box,” so folding often does not work reliably, even when using Value.NativeQuery. If folding breaks, Power BI will load the entire dataset and filter locally, which removes the benefit of incremental refresh.
Recommendation is to use a parameterized SQL query or a view instead of a stored procedure for incremental refresh, as this guarantees folding and predictable refresh behaviour.If you must use a stored procedure:
- Make sure it accepts @StartDate and @EndDate and applies them in the WHERE clause.
- Call it with Value.NativeQuery and [EnableFolding = true].
- Test folding in Power Query (Right-click step → View Native Query).
Kindly refer to the below mentioned documentation links for more information:
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Value.NativeQuery - PowerQuery M | Microsoft Learn
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Thanks!
