Power BI Service刷新后高级数据转换失效问题求助

我在连接SQL数据源的Power Query中完成了高级数据转换，构建了若干列提取产品维度信息。其中Canopy列正常工作，但Shape列存在异常：在Power Query编辑器、Power BI桌面端报表，以及刚发布到Power BI Service时都能正常运行，但数据刷新后，Shape列的计算不再生效。已确认所用的ItemCode列仍存在且数据正确，相关转换代码如下：

#"Canopy column" = Table.AddColumn(dbo_INV1, "Canopy", each if ([Dscription] = null or [Dscription] = "") then "" else if  Text.PositionOf([Dscription], "#")=-1  then "" else Text.End([Dscription], Text.Length([Dscription]) - Text.PositionOf([Dscription], "#"))),
    // -1 means nothing found related to the search otherwise all the text on the right after the #. 
    // First check if the itemcode is null or blank, if so return nothing, if not search for the - in the item text. 
    // If the - is found, search for the shapes in an upper formula and return the shape. 
    #"Shape column" = Table.AddColumn(#"Canopy column", "Shape", each let 
result_search = if [ItemCode] = null or [ItemCode] = "" then "" else if Text.PositionOf([ItemCode],"-")=-1 then "" else Text.Upper(Text.Start([ItemCode],Text.PositionOf([ItemCode], "-"))),
        Substrings = {"SQ", "OCT", "HEX", "RECT", "ROUND"},
        Result = List.First(List.Select(Substrings, (substring) => Text.Contains(result_search, substring))),
        Final = if Result <> null then Result else ""
in Final),

可能的原因及修复方案 #

• 大小写匹配不一致：Text.Contains默认区分大小写，虽然代码中用Text.Upper处理了result_search，但Power BI Service刷新时可能因区域设置或字符编码差异，导致匹配逻辑失效。可以强制忽略大小写：
  修改Result行代码：

  Result = List.First(List.Select(Substrings, (substring) => Text.Contains(result_search, substring, Comparer.OrdinalIgnoreCase))),
  

• 隐藏空白字符干扰：SQL返回的ItemCode可能包含空格、制表符等隐藏空白，导致Text.PositionOf判断错误，进而生成无效的result_search。可以先清理空白：
  修改result_search定义：

  result_search = if [ItemCode] = null or Text.Trim([ItemCode]) = "" then "" else if Text.PositionOf(Text.Trim([ItemCode]),"-")=-1 then "" else Text.Upper(Text.Start(Text.Trim([ItemCode]),Text.PositionOf(Text.Trim([ItemCode]), "-"))),
  

• 服务端表达式优化冲突：Power BI Service的刷新引擎可能对List.Select+List.First的组合做了优化改写，导致逻辑异常。可以换用更直接的匹配方式，比如List.FindText：

  Result = List.First(List.FindText(Substrings, result_search)),
  

  或者Text.AnyOf：

  Result = List.First(Substrings, (sub) => Text.AnyOf(result_search, {sub}, Comparer.OrdinalIgnoreCase)),
  

• 字符编码不兼容：SQL数据库的字符集与Power BI Service使用的字符集不一致，可能导致字符串匹配失败。可以显式转换为Unicode：

  result_search = if [ItemCode] = null or [ItemCode] = "" then "" else if Text.PositionOf(Text.From([ItemCode], "Unicode"),"-")=-1 then "" else Text.Upper(Text.Start(Text.From([ItemCode], "Unicode"),Text.PositionOf(Text.From([ItemCode], "Unicode"), "-"))),
  

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