使用单元格中的值作为电源查询编辑器中存储过程中的参数 [英] Use values in cells as parameters in stored procedure in power query editor

问题描述

我正在构建一个 Excel 表格，该表格将显示日期范围内的销售额.我希望用户能够在单元格中写入/选择日期，而不必在电源查询编辑器中编辑查询.

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.

这里是设置:

我为这篇文章制作了一个测试存储过程来演示我的意思.

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

这是电源查询编辑器中的查询

Here is the Query in the power query editor

从存储过程加载的数据在绿色表中，潜在参数在D和E列中.

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

谢谢

推荐答案

在 Excel 中制作要从表的一部分或命名区域加载值的单元格，然后使用 LoadFromTable 选项将它们加载到 Power Query(加载它们为仅连接"，因此它们不会弹回到 Excel 工作簿中).一旦它们在 Power Query 中，您可以使用 TableName{Row#}[ColumnName] 格式引用它们，甚至可以设置自定义函数来访问它们.

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.

我会发布更详尽的内容，但此类内容已在其他地方广泛讨论.在网络上搜索参数表 Power Query"将返回许多有关执行此操作的各种方法的指南.

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 

这篇关于使用单元格中的值作为电源查询编辑器中存储过程中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！