Help with Parameter

[jim_hubbardUIHS]

Hello, 

I am trying to pass a query parameter to a store procedure and I can get it to work in Power Query but I am trying to let the user select the parameter (A Fixed Date - End of QTR) 

Here is the call to the SP:

let
Source = Sql.Database("uih-sql1", "uihs_datawarehouse"),
Test = Value.NativeQuery(
Source,
"Power_BI_Get_NIRS_QTR @FiscalQTREnd",
[FiscalQtrEnd= NIRS_QTR ])
in
Test

I create a slicer to let the user select the date buth the data never updates...

I set the binding to the parmeter but the data never refreshes. 

 Thanks for any help... I'm missing something....

jim

[jim_hubbardUIHS]

I tried all of the above. It seems like the parameter is working in power query, if I change it there then the stored procedure updates and returns the data, the problem appears to be in the binding of the parameter to a field. I did that but nothing changes when I choose a specific value from the slicer containg the variable. 

[Anonymous]

Hi,

Thanks for the solution TomMartens  offered, and i want to offer some more information for user to refer to.

hello @jim_hubbardUIHS , based on your descriotion, you need to add'exec .....' in your code.

e.g 

let
Source = Sql.Database("uih-sql1", "uihs_datawarehouse",[Query="EXEC Power_BI_Get_NIRS_QTR @FiscalQTREnd ="""& NIRS_QTR & """"]),
....
in
.....

And you can refer to the following link.

Power Query - Function to Execute an SQL Procedure - Hat Full of Data

Solved: Help Needed: Passing Dynamic Parameters to SQL Sto... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @jim_hubbardUIHS ,

do you follow the steps here: https://community.fabric.microsoft.com/t5/Power-Query/Passing-PowerBI-Parameter-To-A-Stored-Procedur... and the linked post?

Keep in mind that this will only work for direct query models.

Regards,

Tom