SQL reporting invalid syntax when run in Power BI

I found time ago the same problem online on power bi site:

http://community.powerbi.com/t5/Desktop/Use-SQL-Store-Procedure-in-Power-BI/td-p/20269

You must be using DirectQuery mode, in which you cannot connect to data with stored procedures. Try again using Import mode or just use a SELECT statement directly.

In DirectQuery mode, PowerBI automatically wraps your query like so: select * from ( [your query] ), and if you attempt this in SSMS with a stored procedure i.e.

select * from (exec dbo.getData)

You get the error you see above.

The solution is you have to place your stored procedure call in an OPENQUERY call to your local server i.e.

select * from OPENQUERY(localServer, 'DatabaseName.dbo.getData')

Prerequisites would be: enabling local server access in OPENQUERY with

exec sp_serveroption @server = 'YourServerName' 
   ,@optname = 'DATA ACCESS' 
   ,@optvalue = 'TRUE' 

And then making sure you use three-part notation in the OPENQUERY as all calls there default to the master database