Forum Discussion
Run SQL Server stored procedure based on parameters
Hi, I am running sql server stored procedure from Power BI and it does bring back the results when I manually set the parameters. However it doesn't work when I define power BI parameters and try...
share your script from advaned editor in power query, you need to use something like this to use parameter #"Parameter Name"
Here is the code with manually typed variables.
How do I replace them with power BI parameters
for example @ValuationRunId = 0 replaced with Poower BI parameter @ValuationRunId = PowerBIParameter
let
Source = Sql.Database("SERVER", "DATABASE", [Query="#(lf)EXEC#(tab) [dbo].[PROCEDURE]#(lf)#(tab)#(tab)@COBDate = '2017-11-02',#(lf)#(tab)#(tab)@ValuationRunDefintion = 'Official EoD',#(lf)#(tab)#(tab)@ValuationRunId = 0,#(lf)#(tab)#(tab)@IgnoreZeroVolume = 0#(lf)#(lf)"])
in
Sourcemany thanks
Any suggestions.
try this, make changes highlighted below.
let Source = Sql.Database("SERVER", "DATABASE",
[Query="#(lf)EXEC#(tab) [dbo].[PROCEDURE]#(lf)#(tab)#(tab)
@COBDate = '" & #"your parameter" & "',#(lf)#(tab)#(tab)@ValuationRunDefintion = 'Official EoD',#(lf)#(tab)#(tab)@ValuationRunId = 0,#(lf)#(tab)#(tab)@IgnoreZeroVolume = 0#(lf)#(lf)"]) in Source
