Use values in cells as parameters in stored proced

问题:

I'm building an excel sheet that will show sales for date range. I want the user to be able to write/select the dates in a cell instead of having to edit the query in the power query editor.

Here is the setup:

I've made a test stored procedure for this post to demonstrate what I mean.

Here is the Query in the power query editor

The loaded data from the stored procedure is in the green table and the potential parameters are in the D and E columns.

Thanks

回答1:

Make the cells you want to load values from part of a table or named range in Excel, then use the LoadFromTable option to load them into Power Query (load them as "Connection Only" so they don't pop back into the Excel workbook). Once they are in Power Query you can reference them with the format TableName{Row#}[ColumnName], or even set up a custom function to access them.

I would post something more thorough but this kind of thing has been extensively covered elsewhere. A web search for "Parameter Table Power Query" will return a number of guides on various ways you can do this.

Added sample of how you would edit the string to include your parameter (I don't know what method you are using to access the date value, but just make sure the date has been converted to a string).

Source = Sql.Database("xxxxxxxxxxxxxxxxx", "xxxxxxxxxxx", [Query=

"#(lf)#(lf)EXEC#(tab) [dbo].[TestParameter]#(lf)
#(tab)#(tab)@from = '" & YourDateMethod & "'#(lf)
#(tab)#(tab)@isValid = 1,#(lf)
#(tab)#(tab)@someInt = 90"

]) 

in Source