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?

Below solution worked for me.Calling SQL Stored Procedure using OPENROWSET

Running SQL Stored Procedure from Power BI Query

ChandanGauns
New Member
2 years ago
Below solution worked for me.
Calling SQL Stored Procedure using OPENROWSET
amustafa
Solution Sage
2 years ago
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
ChandanGauns
New Member
2 years ago
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
  Solution Sage
  2 years ago
  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