I am currently working with PowerQuery and would like to understand if query folding is possible when using a SQL Server stored procedure as a data source.
Specifically, I am executing the following query:
EXEC [my_procedure] '20240831','20240831'
In this case, the stored procedure has two parameters, @fromdate and @todate. My goal is to ensure that query folding is applied to this scenario.
Could you please advise if query folding is supported in this context and, if so, how I might implement it effectively?
If my question is wrong please let me know.
Thank you for your assistance.
Power Query does not support query folding for stored procedure in SQL Server because the execution of a stored procedure is a black-box operation from Power Query perspective. Once you execute the stored procedure, Power Query treats it as a static result set and cannot push additional transformations back to the source.
As a workaround, you can write a SQL query directly in Power Query so it can fold this query back to SQL Server if no transformations are applied afterward that break the folding :
SELECT *
FROM yourtable
WHERE date_column BETWEEN '20240831' AND '20240831'
https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding
https://www.reddit.com/r/PowerBI/comments/wemnd7/query_folding_and_sql_stored_procs/?rdt=39254