I have been following the tutorial HERE but anytime I try to refresh the workbook, it gives me an error of:
DataSource.Error: Microsoft SQL: This database function doesn't support the query option 'Query' with value 'null'.
This is the SQL behind the query, and nothing is returning null, and the stored proc is set-up to accept 3 input parameters.
CSR=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"CSR"],
d1=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d1"],
d2=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"d2"],
Source = Sql.Database("ServerInstanceName", "Credentials",
[Query="exec [dbo].[StoredProcToRun] '" & Number.ToText(d1)
& "''," & Number.ToText(d2) & "','" & CSR & "'"])
And this is the set-up of my stored procedure showing it accepts 3 params:
Alter PROCEDURE dbo.StoredProcToRun
@d1 varchar(50), @d2 varchar(50), @CSR varchar(500)
AS
BEGIN
What do I need to modify in order for this power query to pass the Excel Parameters to my SQL stored procedure and return the data back to the Excel workbook?
I altered my power query sql to the below and now I get an error of
Token comma expected
[Query="exec [dbo].[StoredProcToRun]
& Number.ToText(d1) & "','"
& Number.ToText(d2) & "','"
& CSR & "'"])