☰

在 PowerQuery SQL 查询中参数化 IN 子句

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 power-query
在 PowerQuery SQL 查询中参数化 IN 子句

我见过例子（和编码的）使用从电子表格的表中单元格读取的值来参数化 PowerQuery SQL 语句。但这些总是处理简单的逻辑条件，如myField = <parameter>。我想参数化一个 IN 子句（myField IN <values>），其中从 Excel 表中读取任意数量的values（也在同一个文件中）。有人知道怎么做吗？

答案1

虽然很丑，但是似乎有效。

  1. 创建 PQ 查询以捕获所需参数值的单列“parameterValue”表
  2. 添加虚拟列并按虚拟列对所有行进行分组
  3. 使用 Text.Combine 创建参数列表并删除其他列
  4. 深入到单元格值
  5. 创建不带 IN 子句的所需 SQL 查询（使用“select top 1...”以提高效率）
  6. 使用高级编辑器，修改 SQL 查询删除“top 1”，并接受步骤 1 中的查询作为 IN 参数

查询1：

let
    source = *yourData*,
    addDummy = Table.AddColumn(source, "Custom", each 1),
    group = Table.Group(addDummy, {"Custom"}, {{"Count", each _, type table}}),
    string = Table.AddColumn(group, "queryString", each Text.Combine([Count][parameterValue],"','")),
    removeOtherColumns = Table.SelectColumns(string,{"queryString"}),
    queryString = removeOtherColumns{0}[queryString]
in
    queryString

然后

查询2：

let
    Source = Sql.Database("*serverName*", "*dbName*", [Query=
Text.Replace("
select * from x
where parameterValue in('" & query1 & ")", ")", "')" )
])
in 
Source

答案2

Ken Puls 在他的博客中撰写了一篇关于使用 Power Query 参数表的精彩文章：http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

执行摘要：为参数建立一个表，编写一个函数来读取指定的参数，在 M 代码中使用该函数动态应用所需的参数。

您可能需要扩展此方法以将 SQL 语句构建为字符串。

答案3

我将首先构建一个 Power Query，例如“值”，以读取 Excel 表格行。我将设置“加载到”=“仅连接”。

然后在主要的基于 SQL 的查询中，我将与该新的“值”查询合并，并设置连接类型 = 内部。

相关内容