excel - 替换 Excel 电源查询中的 SQL 查询参数

Question

我有一个工作簿，我在其中使用 SQL 查询的固定参数值从 SQL Server 获取数据。

我想制作另一个工作表并从单元格值中获取 SQL 查询的参数。

我没有找到这方面的任何信息。

另外，我想在另一个工作表中的单元格值发生变化时立即刷新数据。

Answer 1

为此，您需要设置三个不同的部分:

1) Excel 工作表中的参数表

2) PowerQuery 中高级编辑器的更改

3) 当参数表中的任何单元格更改时刷新 PQ 的宏

---

1) Excel 表格

您可以看到我包含了一个名为 param 的列，其中可以包含一个参数名称，以帮助明确哪个参数是哪个。

---

2) PQ 高级编辑器

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = "'" & Text.From(ParamTable[value]{0}) & "'",
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where date >= '2018-01-01' and date < " &Param])
in
    Source

等效替代:(SQL 查询中使用的变量位置的差异。)

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = "'" & Text.From(ParamTable[value]{0}) & "'",
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where date < " &Param & " and date >= '2018-01-01'"])
in
    Source

替代变量类型:(如果处理数字，则不需要字符串标记')

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = Text.From(ParamTable[value]{0}),
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where cnt < " &Param & " and date >= '2018-01-01'"])
in
    Source

说明:

将参数表拉入PQ查询后(ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content])，可以通过列名访问列[value] 和零索引号 {0} 的行。因为我正在提取日期值。我需要将它转换为可以插入到 SQL 查询中的字符串值——因此 Text.From() 和附加的 ''s 到末尾 (< em>SQL 用单个 ' 而不是双 ")

标记字符串

由于我将变量命名为 Param，为了在字符串中使用它，我用 &Param 替换了原来存在的值。

---

2.1 Power Query 的 Value.NativeQuery

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = ParamTable[value]{0},
    Source = Value.NativeQuery(Sql.Database("IP Address", "Database Name"), "Select * from weeks where date < @dateSel and date >= '2018-01-01'",[dateSel = Param])
in
    Source

替代格式:

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = ParamTable[value]{0},
    Source = Sql.Database("IP Address", "Database Name"),
    Data = Value.NativeQuery(Source, "

Select * from weeks
where date < @dateSel and date >= '2018-01-01'

",[dateSel = Param])
in
    Source

注意事项:

使用 Value.NativeQuery() 时，您可以直接将日期或日期时间值作为变量传递，而无需包含单个撇号。

有时将数据检索拆分为 Source 步骤和 NativeQuery 步骤有助于解决 PQ 的零星防火墙问题。

---

3) 宏

这适用于简单检查表中是否有任何更改，然后运行刷新。您需要确保将其放置在正确的模块中。您需要更改的项目是:

• Sheet1 是带有参数表的工作表的代号。
• “表”是参数表的名称
• “Query - Query1”是要刷新的连接的名称

注意: Query1 是查询的名称。连接的默认名称通常是 Query - & query name

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheet1.ListObjects("Table1").DataBodyRange) Is Nothing Then
        ThisWorkbook.Connections("Query - Query1").Refresh
    End If
End Sub