Power Query混合字符串提取数字并实现条件计算需求问询

需求回顾 #

处理混合了数字、文本、特殊字符的Amount列，完成以下操作：

• 提取列中数字转换为数值格式，生成OnlyAmountNr列
• 若内容包含不区分大小写的brutto，将对应数值除以1.5；若包含CHF，则除以1.9
• 移除错误值、#VALUE!及空值

优化后的M代码 #

let
    Quelle = Sql.Databases("ukTest123.database.windows.net"),
    CSD_DE_RawData = Quelle{[Name="CSD_DE_RawData"]}[Data],
    money_ASD_Applications = CSD_DE_RawData{[Schema="money", Item="ASD_Applications"]}[Data],
    // 过滤无效行：移除空值、#VALUE!及错误值
    FilterInvalidRows = Table.SelectRows(money_ASD_Applications, each not List.Contains({null, "#VALUE!"}, [Amount]) and not Error.Is([Amount])),
    // 添加自定义列完成数字提取、关键字判断与计算
    ProcessAmount = Table.AddColumn(FilterInvalidRows, "ProcessedAmount", each 
        let
            // 提取所有数字字符（包含小数点）
            numText = Text.Select([Amount], {"0".."9", "."}),
            // 转换为数值，处理空提取结果的情况
            amountNr = try Number.From(numText) otherwise null,
            // 判断关键字并计算
            isBrutto = Text.Contains([Amount], "brutto", Comparer.OrdinalIgnoreCase),
            isCHF = Text.Contains([Amount], "CHF", Comparer.OrdinalIgnoreCase),
            calculatedValue = 
                if amountNr = null then null
                else if isBrutto then amountNr / 1.5
                else if isCHF then amountNr / 1.9
                else amountNr
        in
            calculatedValue),
    // 重命名列并调整类型
    RenameAndType = Table.RenameColumns(ProcessAmount, {{"ProcessedAmount", "OnlyAmountNr"}}),
    FinalTable = Table.TransformColumnTypes(RenameAndType, {{"OnlyAmountNr", type number}})
in
    FinalTable

关键优化说明 #

• 合并冗余步骤：将原代码中多列的提取、判断逻辑合并到单个自定义列中，减少中间冗余列，提升可读性和性能
• 简化大小写判断：使用Text.Contains的第三个参数Comparer.OrdinalIgnoreCase，直接实现不区分大小写的关键字匹配，无需多次判断
• 提前过滤无效行：先移除空值、#VALUE!和错误行，避免后续处理无效数据
• 异常处理：用try otherwise处理数字提取失败的情况，避免转换错误
• 逻辑清晰化：将数字提取、关键字判断、计算拆分到子步骤中，每一步职责明确，便于维护

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