Run SQL Server stored procedure based on parameters

[Student225]

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 and use them instead.
Here is what I want to do,
1. Define Power BI parameters
2. Use them in SQL statement such as,

EXEC       [dbo].[Some_Procedure]
            @Date = PowerBIDefinedParameter

thanks.

[v-jiascu-msft]

Hi @Student225,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[parry2k]

share your script from advaned editor in power query, you need to use something like this to use parameter #"Parameter Name" 

[Student225]

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
    Source

many thanks

[Student225]

Any suggestions.

[parry2k]

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