Power Query优化：状态文本列转布尔列的更优实现方式

现有一段Power Query代码，通过多条件判断将「Source for process flows」列转换为名为「Has process source」的布尔列，原代码如下：

NextStep = Table.AddColumn(
    #"Clear nulls (approx 3)", 
    "Has process source", 
    each 
        [Source for process flows] <> "Source - No" and 
        [Source for process flows] <> "Source not found" and 
        Text.Lower([Source for process flows]) <> "source not available" and 
        [Source for process flows] <> null and 
        [Source for process flows] <> "?", 
    type logical
)

咨询是否有更简洁的实现方法，需求为将包含状态更新的文本列转换为True/False布尔列。

方法1：用List.Contains反向判断（统一大小写） #

把所有不符合条件的值整理成一个列表，统一转小写后判断是否不在列表中，同时处理空值：

NextStep = Table.AddColumn(
    #"Clear nulls (approx 3)", 
    "Has process source", 
    each 
        not List.Contains(
            {"source - no", "source not found", "source not available", "?"},
            if [Source for process flows] = null then null else Text.Lower([Source for process flows])
        ) and [Source for process flows] <> null,
    type logical
)

嫌麻烦的话，直接把空值也放进排除列表，代码更紧凑：

NextStep = Table.AddColumn(
    #"Clear nulls (approx 3)", 
    "Has process source", 
    each 
        not List.Contains(
            {null, "source - no", "source not found", "source not available", "?"},
            if [Source for process flows] = null then null else Text.Lower([Source for process flows])
        ),
    type logical
)

方法2：处理潜在空白字符的版本 #

如果原列里可能有空格、换行这类隐藏空白字符，可以先用Text.Clean清理后再判断，避免误判：

NextStep = Table.AddColumn(
    #"Clear nulls (approx 3)", 
    "Has process source", 
    let
        cleanedValue = if [Source for process flows] = null then null else Text.Lower(Text.Clean([Source for process flows]))
    in
        not List.Contains({null, "source - no", "source not found", "source not available", "?"}, cleanedValue),
    type logical
)

方法3：用try otherwise简化空值处理 #

针对空值的情况，用try块可以省去单独的空值判断，代码更简洁：

NextStep = Table.AddColumn(
    #"Clear nulls (approx 3)", 
    "Has process source", 
    each 
        not List.Contains(
            {"source - no", "source not found", "source not available", "?"},
            Text.Lower([Source for process flows])
        ) otherwise false,
    type logical
)

这里otherwise false会在原字段为null时直接返回false，完全符合需求。

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