Can I execute a SP in DirectQuery Mode with or without parameters?

Hi Anonymous ,

Please review the content in the following blogs and check whether they can help you achieve the requirement.

The first method:

Power BI DirectQuery with Parameterized Stored Procedure

1. Please set the source as below in Advanced Editor:

= Sql.Database("xxxxx", "AdventureWorksDW2014", [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 
     'exec AdventureWorksDW2014..getProcategory')", CreateNavigationProperties=false])

2. By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. So you will get the below error message. When this option is set to 1, SQL Server allows ad hoc access. Then you can connect to sql server successfully...

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO  

ad hoc distributed queries Server Configuration Option

The second method:

SQL Server and Power BI: How to load Stored Procedure data into SQL Server with DirectQuery

Best Regards