Power Query：基于含可变分类的另一表为行匹配对应值

前提说明 #

假设table2中的BIN字段为类似"0-10000"、"10001-20000"的区间文本，若你的BIN格式不同，可对应调整拆分逻辑。

步骤1：处理价格表的区间字段 #

加载table2后，拆分BIN的上下限：

• 添加自定义列BIN下限，公式：Number.From(Text.BeforeDelimiter([BIN], "-"))
• 添加自定义列BIN上限，公式：Number.From(Text.AfterDelimiter([BIN], "-"))
• 可删除原BIN列，保留ID、BIN下限、BIN上限、price字段

步骤2：合并订单表与处理后的价格表 #

加载table1，执行合并操作：

• 选择table1的id字段，与处理后的table2的ID字段做左外部合并
• 合并后展开新列，选择BIN下限、BIN上限、price字段

步骤3：筛选匹配区间的价格 #

添加自定义列匹配价格，公式：

if [quantity] >= [BIN下限] and [quantity] <= [BIN上限] then [price] else null

随后按Order分组提取有效价格：

• 选中Order、quantity、id字段，点击「分组依据」，操作选「所有行」，新列名设为匹配记录
• 添加自定义列，公式：List.RemoveNulls(Table.Column([匹配记录], "匹配价格")){0}，命名为price
• 删除多余的匹配记录列，整理出最终表

完整M代码示例 #

let
    // 加载并处理价格表
    源_price = Excel.CurrentWorkbook(){[Name="table2"]}[Content],
    拆分BIN下限 = Table.AddColumn(源_price, "BIN下限", each Number.From(Text.BeforeDelimiter([BIN], "-"))),
    拆分BIN上限 = Table.AddColumn(拆分BIN下限, "BIN上限", each Number.From(Text.AfterDelimiter([BIN], "-"))),
    清理价格表 = Table.RemoveColumns(拆分BIN上限,{"BIN"}),

    // 加载订单表
    源_order = Excel.CurrentWorkbook(){[Name="table1"]}[Content],

    // 合并两张表
    合并表 = Table.NestedJoin(源_order, {"id"}, 清理价格表, {"ID"}, "价格数据", JoinKind.LeftOuter),
    展开价格数据 = Table.ExpandTableColumn(合并表, "价格数据", {"BIN下限", "BIN上限", "price"}, {"BIN下限", "BIN上限", "price"}),

    // 匹配区间并提取价格
    添加匹配列 = Table.AddColumn(展开价格数据, "匹配价格", each if [quantity] >= [BIN下限] and [quantity] <= [BIN上限] then [price] else null),
    分组订单 = Table.Group(添加匹配列, {"Order", "quantity", "id"}, {{"匹配记录", each _, type table [Order=text, quantity=number, id=text, BIN下限=number, BIN上限=number, price=number, 匹配价格=number]}}),
    提取有效价格 = Table.AddColumn(分组订单, "price", each List.RemoveNulls(Table.Column([匹配记录], "匹配价格")){0}),
    清理最终表 = Table.RemoveColumns(提取有效价格,{"匹配记录"})
in
    清理最终表

注意事项 #

• 若BIN区间为左闭右开规则（如0-10000表示≥0且<10000），需将自定义列判断公式中的<=改为<
• 确保quantity、BIN下限、BIN上限均为数值类型，若不是，需先用Table.TransformColumns转换类型

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