PowerBI PowerQuery If-Then语句Else分支失效问题排查

我的数据表中LINE_OF_BUSINESS字段本该从其他系统获取数据，但有部分值为空。我尝试通过PRIMARY_TECHNOLOGY字段推断该字段值，写了如下If语句代码并设置了默认值，但处理后仍存在空值。

= Table.ReplaceValue(#"UNKNOWN Region",
    each [LINE_OF_BUSINESS],
    each
    if  
        ([LINE_OF_BUSINESS] is null) then
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Print Management")) then "DIR" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Office Solutions")) then "DIR" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Scan Capture Route")) then "DIR" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Workplace of the Future")) then "DIR" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Fax Solutions")) then "DIR" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Digital WorkPlace")) then "DIR" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "AppDev")) then "MIT" else
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Cloud")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Messaging")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Networking")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Security")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Server")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Service")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Virtualization")) then "MIT" else 
      if (Text.Contains([PRIMARY_TECHNOLOGY], "Other")) then "MIT" else "DIR"

      else
      [LINE_OF_BUSINESS] ,
Replacer.ReplaceValue,{"LINE_OF_BUSINESS"})

核心问题 #

处理后仍有空值，主要原因有两点：

1. PRIMARY_TECHNOLOGY字段本身为空时，Text.Contains会触发错误，导致整个条件分支失效，无法生成默认值；
2. 原代码的多层If判断未处理大小写差异、多余空格等情况，可能导致部分匹配规则失效。

修复方案 #

通过提前处理空值、简化匹配逻辑、统一字符格式来解决问题，修改后的代码如下：

= Table.ReplaceValue(#"UNKNOWN Region",
    each [LINE_OF_BUSINESS],
    each
    if [LINE_OF_BUSINESS] is null then
        let
            // 统一转小写，同时处理PRIMARY_TECHNOLOGY为空的情况
            techLower = if [PRIMARY_TECHNOLOGY] is null then "" else Text.Lower([PRIMARY_TECHNOLOGY]),
            // 按业务分类整理关键词列表
            dirKeywords = {"print management", "office solutions", "scan capture route", "workplace of the future", "fax solutions", "digital workplace"},
            mitKeywords = {"appdev", "cloud", "messaging", "networking", "security", "server", "service", "virtualization", "other"}
        in
            // 批量判断关键词匹配
            if List.AnyTrue(List.Transform(dirKeywords, (kw) => Text.Contains(techLower, kw))) then "DIR"
            else if List.AnyTrue(List.Transform(mitKeywords, (kw) => Text.Contains(techLower, kw))) then "MIT"
            else "DIR"
    else
        [LINE_OF_BUSINESS],
Replacer.ReplaceValue,{"LINE_OF_BUSINESS"})

代码说明 #

• 先对PRIMARY_TECHNOLOGY做空值处理并统一转小写，避免Text.Contains报错，同时消除大小写差异导致的匹配失败；
• 用关键词列表替代多层If判断，代码更简洁易维护，后续新增关键词只需修改列表即可；
• 无论PRIMARY_TECHNOLOGY是否为空，最终都会返回DIR或MIT，不会出现空值。

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