如何在Power Query的SQL WHERE子句中使用Excel单元格值作为字符串

问题诊断 #

你的代码存在两个核心问题：

1. SQL语句语法不完整：Sql.Database的Query参数字符串未闭合，且缺少记录的闭合括号
2. 参数类型不匹配：年份字段（year）通常为数字类型，你用单引号包裹YearSelect会导致SQL语法错误

修正后的M代码 #

情况1：SQL表中year字段为数字类型 #

YearSelect = Number.From(Excel.CurrentWorkbook(){[Name="YearSelect"]}[Content]{0}[Column1]),
    Source = Sql.Database("<Server>", "<Database>", [Query="SELECT Packnum, Max(year) as Year 
                                                      FROM PIC704Current 
                                                      WHERE year <= " & Text.From(YearSelect) & " 
                                                        AND DiscountReasonCode Not in ('LT','UP') 
                                                        AND RD >= 50 
                                                        AND CatID not in ('H4','G2') 
                                                      GROUP BY PackNum"])
in
    Source

情况2：SQL表中year字段为文本类型 #

YearSelect = Excel.CurrentWorkbook(){[Name="YearSelect"]}[Content]{0}[Column1],
    Source = Sql.Database("<Server>", "<Database>", [Query="SELECT Packnum, Max(year) as Year 
                                                      FROM PIC704Current 
                                                      WHERE year <= '" & YearSelect & "' 
                                                        AND DiscountReasonCode Not in ('LT','UP') 
                                                        AND RD >= 50 
                                                        AND CatID not in ('H4','G2') 
                                                      GROUP BY PackNum"])
in
    Source

关键注意事项 #

• 命名范围确认：确保Excel单元格I2已被命名为YearSelect（公式选项卡→定义名称）
• 类型转换：用Number.From()将Excel单元格内容转为数字，避免文本格式干扰；再用Text.From()将数字转为字符串用于SQL拼接
• 调试技巧：可添加临时步骤查看生成的SQL语句，验证语法是否正确：

  YearSelect = Number.From(Excel.CurrentWorkbook(){[Name="YearSelect"]}[Content]{0}[Column1]),
      SqlQuery = "SELECT Packnum, Max(year) as Year FROM PIC704Current WHERE year <= " & Text.From(YearSelect) & " AND DiscountReasonCode Not in ('LT','UP') AND RD >= 50 AND CatID not in ('H4','G2') GROUP BY PackNum",
      Source = Sql.Database("<Server>", "<Database>", [Query=SqlQuery])
  in
      Source
  

内容的提问来源于stack exchange，提问作者Deke