Power Query报错：无法对文本与数字应用&运算符，如何嵌入数值参数到TSQL？

错误原因 #

报错We cannot apply operator & to types Text and Number.的核心原因是：你定义的TestID是数值类型，而M语言中&运算符仅支持文本（Text）类型之间的拼接，直接将数值与SQL字符串拼接会触发类型不匹配。

解决方案1：将数值参数转为文本后拼接 #

用Text.From()函数把数值类型的TestID转换为文本，再进行字符串拼接。修改后的M代码如下：

let 
    TestID = Excel.CurrentWorkbook(){[Name="TestID"]}[Content]{0}[Column1],
    // 将数值转为文本
    TestID_Text = Text.From(TestID),
    Source = Sql.Database("SERVERNAME", "DBNAME", 
    [Query="
SELECT *
from [POD01RAWDATA]
where 
time@timestamp 
between 
(select distinct max([time@timestamp]) from [POD01EVENTLOG] where [data_format_4] = 1 and [data_format_0] = "&TestID_Text&")
and 
(select distinct max([time@timestamp]) + 60 from [POD01EVENTLOG] where [data_format_4] = 1 and [data_format_0] = "&TestID_Text&")
"])
in
    Source

解决方案2：使用参数化查询（推荐，避免SQL注入） #

直接字符串拼接存在SQL注入风险，更规范的方式是利用Sql.Database的Parameters参数实现安全的参数化查询，无需手动转换类型：

修改后的M代码 #

let 
    TestID = Excel.CurrentWorkbook(){[Name="TestID"]}[Content]{0}[Column1],
    Source = Sql.Database("SERVERNAME", "DBNAME", 
    [Query="
SELECT *
from [POD01RAWDATA]
where 
time@timestamp 
between 
(select distinct max([time@timestamp]) from [POD01EVENTLOG] where [data_format_4] = 1 and [data_format_0] = @TestID)
and 
(select distinct max([time@timestamp]) + 60 from [POD01EVENTLOG] where [data_format_4] = 1 and [data_format_0] = @TestID)
", 
    Parameters = [TestID = TestID]
    ])
in
    Source

说明 #

• TSQL中用@TestID作为参数占位符
• 在M代码的Parameters参数中传入键值对，键名需与TSQL中的占位符一致，值为你的TestID参数，Power Query会自动处理类型匹配，同时避免SQL注入风险。

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