How to utilise stored procedure with temp tables in Power BI?

[SJi]

I'd like to use stored procedure containing temp tables as data source for Power Bi.

I've tried:

[Query="EXECUTE [kpi].[KPI_NV]"])

and

Sql.Database("CZPHADDWH01\DEV", "DWH_Staging", [Query="EXECUTE [kpi].[KPI_NV]"])

which both raises an error

Details: "Microsoft SQL: Incorrect syntax near the keyword 'Database'. Unclosed quotation mark after the character string '])'."

Using openquery (not prefered)

SELECT *
FROM OPENQUERY ([CZPHADDWH01\DEV], 'EXEC DWH_Staging.kpi.KPI_NV')

which raises

Details: "Microsoft SQL: The metadata could not be determined because statement 'INSERT #Regions (RegionId,[SalesRegionName]) SELECT [SalesRegionId],[SalesRegionName] FROM dim' in procedure 'KPI_NV' uses a temp table."

[v-yuezhe-msft]

@SJi,

Please use WITH RESULT SET syntax instead of OPENQUERY.  There is a similar thread for your reference.

https://www.experts-exchange.com/questions/28228965/Openquery-in-SQL-Server-2012-How-can-I-make-this...

Regards,
Lydia