Power Query跨表匹配带标识项目名并调和数据的高效月度方案咨询

问题分析 #

你之前的公式返回全"No Match"，是因为#"Table1"[PROJ]是引用Table1的整个PROJ列，而非当前行对应的匹配值，无法实现逐行匹配。以下是基于Power Query的高效解决方案，完全适配你的业务规则：

分步实现步骤 #

1. 预处理Table1：生成匹配用的标准项目名 #

为Table1添加标准项目名列，移除名称中的AB和DDPC标识，使其与Table2的项目名称格式统一：

// 为Table1添加标准项目名
Table1处理 = Table.AddColumn(Table1, "标准项目名", each Text.Replace(Text.Replace([项目名称], "AB", ""), "DDPC", ""))

随后重命名Table1的数值列避免冲突：

Table1处理 = Table.RenameColumns(Table1处理, {{"利润", "利润_T1"}, {"工时", "工时_T1"}, {"销售额", "销售额_T1"}})

2. 预处理Table2：重命名数值列 #

同样重命名Table2的数值列，方便后续计算：

Table2处理 = Table.RenameColumns(Table2, {{"利润", "利润_T2"}, {"工时", "工时_T2"}})

3. 合并两表并匹配数据 #

以Table2为主表，通过项目名称（Table2）和标准项目名（Table1）做左外连接，确保所有Table2的项目都能保留：

合并查询 = Table.NestedJoin(Table2处理, {"项目名称"}, Table1处理, {"标准项目名"}, "Table1数据", JoinKind.LeftOuter)

展开合并后的Table1数据列：

展开数据 = Table.ExpandTableColumn(合并查询, "Table1数据", {"利润_T1", "工时_T1", "销售额_T1"}, {"利润_T1", "工时_T1", "销售额_T1"})

4. 按规则计算最终列 #

根据业务规则生成最终的数值列：

• 利润：取两表对应值的平均值
• 工时：取两表对应值的和
• 销售额：直接复用Table1的数值

计算最终列 = Table.AddColumn(展开数据, "利润", each ([利润_T2] + [利润_T1])/2)
计算最终列 = Table.AddColumn(计算最终列, "工时", each [工时_T2] + [工时_T1])
计算最终列 = Table.RenameColumns(计算最终列, {{"销售额_T1", "销售额"}})

5. 清理冗余列 #

删除不需要的中间列，保留最终需要的字段：

最终表 = Table.SelectColumns(计算最终列, {"项目名称", "利润", "工时", "销售额"})

完整可复用M代码 #

let
    // 处理Table1
    Table1处理 = 
        let
            源 = Table1,
            添加标准项目名 = Table.AddColumn(源, "标准项目名", each Text.Replace(Text.Replace([项目名称], "AB", ""), "DDPC", "")),
            重命名列 = Table.RenameColumns(添加标准项目名, {{"利润", "利润_T1"}, {"工时", "工时_T1"}, {"销售额", "销售额_T1"}}),
            保留必要列 = Table.SelectColumns(重命名列, {"标准项目名", "利润_T1", "工时_T1", "销售额_T1"})
        in
            保留必要列,

    // 处理Table2
    Table2处理 = 
        let
            源 = Table2,
            重命名列 = Table.RenameColumns(源, {{"利润", "利润_T2"}, {"工时", "工时_T2"}})
        in
            重命名列,

    // 合并与计算
    合并查询 = Table.NestedJoin(Table2处理, {"项目名称"}, Table1处理, {"标准项目名"}, "Table1数据", JoinKind.LeftOuter),
    展开数据 = Table.ExpandTableColumn(合并查询, "Table1数据", {"利润_T1", "工时_T1", "销售额_T1"}, {"利润_T1", "工时_T1", "销售额_T1"}),
    计算利润 = Table.AddColumn(展开数据, "利润", each ([利润_T2] + [利润_T1])/2),
    计算工时 = Table.AddColumn(计算利润, "工时", each [工时_T2] + [工时_T1]),
    整理销售额 = Table.RenameColumns(计算工时, {{"销售额_T1", "销售额"}}),
    清理列 = Table.SelectColumns(整理销售额, {"项目名称", "利润", "工时", "销售额"})
in
    清理列

补充说明 #

• 如果存在匹配失败的项目，可在计算列中添加空值处理，例如利润列改为：each if [利润_T1] <> null then ([利润_T2] + [利润_T1])/2 else [利润_T2]
• 该方案支持每月批量更新，只需替换Table1和Table2的数据源即可自动完成调和

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