如何简化Power Query M中新增条件列的多分支else if语句

方法1：分类映射表（推荐，适配每月更新场景） #

把商户匹配规则单独做成一个可编辑的表，后续维护分类只需要更新这个表，无需修改主查询代码。

操作步骤： #

1. 新建空白查询并命名为MerchantCategoryRules，粘贴以下代码生成规则表：

let
    Source = Table.FromRecords({
        [Keyword = "Farmer's Market", Category = "Grocery"],
        [Keyword = "CFSD", Category = "School"],
        [Keyword = "76", Category = "Gas"],
        [Keyword = "Wildflower", Category = "Grocery"],
        [Keyword = "Chvrn", Category = "Gas"]
    })
in
    Source

2. 在原查询中替换原有条件列代码，使用以下逻辑匹配分类：

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows1", "Category", each 
    let
        matchedRule = Table.SelectRows(MerchantCategoryRules, (rule) => Text.Contains([Merchant], rule[Keyword])),
        result = if Table.RowCount(matchedRule) > 0 then matchedRule[Category]{0} else "Misc"
    in
        result
)

方法2：代码内定义规则列表 #

如果不需要单独建表，可以把规则直接写在代码里，通过列表筛选简化嵌套逻辑：

#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows1", "Category", each 
    let
        rules = {
            {"Farmer's Market", "Grocery"},
            {"CFSD", "School"},
            {"76", "Gas"},
            {"Wildflower", "Grocery"},
            {"Chvrn", "Gas"}
        },
        matched = List.Select(rules, (r) => Text.Contains([Merchant], r{0})),
        result = if List.Count(matched) > 0 then matched{0}{1} else "Misc"
    in
        result
)

注意事项： #

• 两种方法都和原else if逻辑一致：从上到下匹配第一个符合条件的关键词，返回对应分类。
• 方法1更适合每月有规则变动的场景，直接编辑规则表即可，无需改动主代码，降低维护成本。

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