Running SQL Stored Procedure from Power BI Query

[ChandanGauns]

Hi,

I am using DirectQuery Data Mode to fetch data from SQL Server for my Power BI Report. I followed the below steps:

Above configuration generated the following query:

When I open the Query, I can see the preview of that SP's output.

But when I click on Close and Apply, its giving me the following error:

How do I fix this?

[ChandanGauns · Accepted solution]

Below solution worked for me.

Calling SQL Stored Procedure using OPENROWSET 

[ChandanGauns]

hi @amustafa 

Below is my query:

let
    Source = Sql.Database("1.2.3.4,1234", "MyDB", [Query="EXEC [dbo].[MyStoredProc] '2023'"])
in
    Source

I tried using your solution but its not working as expected.

[amustafa]

I was able to run the following query in Direct Query mode instead of Import Query and load the data form Power Query just fine. I have also made it a paramaterized query. 

let
CustomerID = customeridparam,
Source = Sql.Database("localhost", "AdventureWorks",
[Query="EXEC GetCustomerOrderSummary @customer_id = " & Text.From(CustomerID)])
in
Source

[amustafa]

Can you please paste your full M Code from Power Query? Here's a general code with correct syntax.

let
Source = Sql.Database(ServerName, DatabaseName),
Query = "EXEC YourStoredProcedure @Parameter1 = Value1, @Parameter2 = Value2",
Result = Source{[Schema="dbo",Item="YourStoredProcedure"]}[Data]
in
Result