PowerBI run SP in DirectQuery

When I try to run a Stored Procedure in PowerBI getting this error 

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.

You may need to configure SQL Server , but after all setup , you have to do following

Create the SP, and use in OPENROWSET as following , test this is in SSMS

SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

If this is working fine create a view  as following:-

Create view [dbo].[VTest_dataTB_Delthis] as

 SELECT * FROM OPENROWSET('SQLNCLI','server=DEVSERVER;Persist Security Info=True;UID=User;PWD=Password',

'exec budget.[dbo].[SP_TB_Test_Delthis_new]')

 

use this in Power BI as following :

Ad Hoc Distributed Queries turned off as part of the security configuration

 Msg 15281, Level 16, State 1, Procedure PBIView_encrypted_Test1, Line 6 [Batch Start Line 0]

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Run Following-

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

GO

REBUILD indexes and update Stat SQL Server

Sometime when you load data, we are not able to get fast access using application. In order to fix this do following:- 

 ALTER INDEX ALL ON TBL_PlanBUDGET REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = 80)

Update STATISTICS TBL_PlanBUDGET