Incremental Refresh - Stored Procedure in SQL Server

[Max_701]

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!

[v-kpoloju-msft · Accepted solution]

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.

[Max_701]

Thanks! 

[GilbertQ]

Hi @Max_701 

I'm not sure that this is 100 percent it can be done with a stored procedure, but you can certainly do this with the database view. That might be an alternative option.