How to use stored procedure to get data?

[SJi]

Hello,

we have stored procedures made for our mobile and paginated reports and we would like to use them with PowerBI. The procedures often have temp tables and parameters. I've enabled opequery on our database, however I'm getting an error as show below. How can be stored procedure used to get data in PowerBI? Any help would be appreciated.

Details: "Microsoft SQL: The metadata could not be determined because statement 'SELECT 
		ROW_NUMBER () OVER (PARTITION BY a.MonthBack ORDER BY sum(a.ApprovedLoanAmount) DESC)	
AS' in procedure 'MR0001_MosRanking' uses a temp table."

[SJi]

I've found there're basicaly 3 ways how to utilise stored procedure in Power BI:

1. Using and executing variable
2. Openquery
3. Table-Valued Function

It seems, the most straightforward way is to execute variable which stores stored procedure call, however I'm having some troubles in doing so.

[v-yulgu-msft]

Hi @SJi,

Thanks for your sharing.

Regards,

Yuliana Gu