Hi @ms527
Based on the details you provided, it appears that you are encountering limitations related to using native queries and stored procedures with Power BI's incremental refresh and data source management features.
Power BI's incremental refresh feature does not support using stored procedures directly, as described in the following link.
Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn
Incremental refresh requires the ability to apply filters directly on the query to determine what data has changed, which is not possible with stored procedures because they encapsulate SQL queries.
Regarding the issue of the datasource option being greyed out, this is likely due to the use of a native query.
When you use a native query (), Power BI treats this as a custom SQL statement and limits certain functionalities, including changing the datasource through the UI, as it cannot guarantee that the new datasource will support the custom SQL without errors.
You can try the following to resolve the issue:
Modify the Approach for Incremental Refresh: Instead of using a stored procedure, consider implementing the logic directly in Power Query M language or using a view in your database that can be parameterized within Power BI.
This allows Power BI to manage the query folding and apply the necessary filters for incremental refresh.
Changing the Datasource: To change the datasource, you might need to manually edit the Power Query M code to replace the server and database names.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
