Passing a parameter to a query in Power Query for a SQL stored proc

[the606]

Hi,

I need to pass a parameter to be sent to a stored proc in SQL. I have created a parameter on an excel sheet looking at a field called date_parameter. This is showing up as a parameter in power query, the @asatdate  works if i put in a date instead of the parameter (example = Sql.Database("IP_Address", "DB_NAME", [Query="execute UDEF_BI_DEF_AGED_DEBTORS_EXTENDED @ASATDATE = '06/08/2021'"])

I have tried various mmethods of passing this across and i cant seem to get the styntax correct.

Any ideas?

= Sql.Database("IP_Address", "DB_NAME", [Query="execute UDEF_BI_DEF_AGED_DEBTORS_EXTENDED @ASATDATE = Parameter[Date_Parameter]{0}"])

This is my latest try!

[v-kelly-msft · Accepted solution]

Hi  @the606 ,

You can create a custom function in power query then use it as parameter to execute a Stored Procedure.

Check the reference below:

 https://docs.microsoft.com/en-us/power-query/custom-function

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[CNENFRNL]

Value.NativeQuery comes to your rescue,

https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...