在 Power Query 中使用单元格值作为参数值

Question

我在 PowerQuery Excel 2016 中使用如下查询：

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '03'  ")
in
    Source

我想用单元格中的值替换“03”AD2

这可能吗？

Answer 1

Rajesh S 的回答已经可以满足您的要求了。但是，他的答案的弱点是您的参数取决于其在表格上的位置。我建议一个更好的解决方案：

1. 正如 Rajesh 所建议的，创建一个带有适当标题的表格：
2. 确保光标位于表格内，在“数据”选项卡上，单击“来自表格/范围”
3. 现在，您将进入 Power Query 编辑器。首先，确保“值”列的数据类型为“文本”。接下来，单击“参数名称”列，然后在“转换”选项卡下单击“数据透视列”。使用“值”列作为值。确保展开“高级选项”选项卡，并选择“不聚合”作为“聚合值函数”。
4. 此时，您将拥有以参数名称作为列名的不同列。右键单击“参数”查询，然后单击“引用”
5. 右键单击参数值，然后单击“向下钻取”
6. 现在您将拥有一个可用作工作表变量的查询。继续在 ODBC 查询中使用它：

let Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '"&STN1"' ") in Source

我知道我的步骤看起来很繁琐，但我记性不好，所以我需要使用描述性变量名来轻松记住我的 Power Query 的作用。如果您想使用单元格值与其他查询进行计算，您也可以在旋转参数后执行“更改类型”步骤。这是我的参考

Answer 2

我认为对于其他两个答案都有一种更好、更友好的方法，它既是动态的，并且除了参数本身之外不会创建额外的条目：

在 Excel 中：

1. 创建一个桌子参数：
2. 前往表格工具选项卡并将表重命名为参数。

然后在 Power Query 中：

1. 选择来自其他来源 => 空白查询
2. 去视图 => 高级编辑器
3. 将所有代码替换为以下内容：

 

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

最后，单击“完成”，并将函数重命名为获取参数。

从那时起，你就可以fnGetParameter("STN1")在任何地方使用它 - 在这种特定情况下 - 例如：

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '" & fnGetParameter("STN1") & "'")
in
    Source

参考： https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

Answer 3

现在，使用最新版本的 Excel (O365) 可以更轻松地完成此操作：您可以使用高级编辑器简化在 PowerQuery 请求中声明变量的过程，指示必须从 Excel 表中获取变量值，然后在以下步骤中引用变量。

例如 ：myVar = Excel.CurrentWorkbook(){[Name="MyParameterTable"]}[Content]{0}[MyColumnName]

因此，使用具有键/值列的参数表，下面是我如何使要导入的 .csv 文件的目录和文件名动态化

let
    directy = Excel.CurrentWorkbook(){[Name="Param"]}[Content]{0}[Value],
    newDirectory = if Text.EndsWith(directy,"\") then directy else directy&"\",
    filePath = Excel.CurrentWorkbook(){[Name="Param"]}[Content]{1}[Value],
    Source = Csv.Document(File.Contents(newDirectory&filePath),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source

参数表示例：

在高级编辑器中编辑的查询示例：

Answer 4

不，您不能直接使用单元格引用。您需要做的是创建一个单独的数据区域并将其转换为表格。

现在将此范围转换为表并分配一个适当的名称，如 ParaTAB 或 ParameterTAB，然后您的命令应该是这样的，

let
   Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 =  ParaTAB[Value]{2}  ")
in
    Source

这里 Power Query 将选择第三个值或第三行，即 ADM，因为它从 0 开始计数。

注意：请记住 B6 和 A2 是单元格引用。