如何在Power Query/DAX中合并新旧包成分表并避免重复行

Power Query 批量处理方案 #

操作步骤 #

1. 规范表名与字段

   • 将三个表重命名为OldPackages（旧包：旧包名称、成分）、NewPackages（新包：新包名称、成分）、PackageMapping（映射：旧包名称、新包名称），加载到Power Query编辑器。

2. 关联映射表与旧包成分

   • 选中PackageMapping，点击顶部「合并查询」，选择OldPackages，关联字段为旧包名称，合并类型选左外部。
   • 展开合并后的OldPackages列，仅保留成分字段，重命名为旧成分。

3. 关联结果与新包成分

   • 继续点击「合并查询」，选择NewPackages，关联字段为新包名称，合并类型选左外部。
   • 展开合并后的NewPackages列，仅保留成分字段，重命名为新成分。

4. 标记成分状态

   • 添加自定义列，输入以下M语言代码：

     if [旧成分] = [新成分] then "KEPT"
     else if [旧成分] <> null and [新成分] = null then "REMOVED"
     else if [旧成分] = null and [新成分] <> null then "NEW"
     else null
     

   • 过滤掉状态列为空的无效行，最终保留旧包名称、新包名称、旧成分、新成分、状态列，加载回Power BI。

DAX 计算表方案 #

实现步骤 #

1. 建立模型关系

   • 在Power BI模型视图中，创建：
     • PackageMapping[旧包名称] ↔ OldPackages[旧包名称]（一对多）
     • PackageMapping[新包名称] ↔ NewPackages[新包名称]（一对多）

2. 创建计算表

   • 新建计算表，输入以下DAX表达式：

     PackageComparison = 
     VAR OldWithMapping = 
         SELECTCOLUMNS(
             NATURALLEFTOUTERJOIN(PackageMapping, OldPackages),
             "旧包名称", OldPackages[旧包名称],
             "新包名称", PackageMapping[新包名称],
             "成分", OldPackages[成分],
             "类型", "OLD"
         )
     VAR NewWithMapping = 
         SELECTCOLUMNS(
             NATURALLEFTOUTERJOIN(PackageMapping, NewPackages),
             "旧包名称", PackageMapping[旧包名称],
             "新包名称", NewPackages[新包名称],
             "成分", NewPackages[成分],
             "类型", "NEW"
         )
     VAR Combined = UNION(OldWithMapping, NewWithMapping)
     VAR StatusCalculated = 
         ADDCOLUMNS(
             Combined,
             "状态",
             VAR CurrentComponent = [成分]
             VAR CurrentOldPackage = [旧包名称]
             VAR CurrentNewPackage = [新包名称]
             VAR IsInOld = COUNTROWS(FILTER(OldWithMapping, [旧包名称]=CurrentOldPackage && [成分]=CurrentComponent)) > 0
             VAR IsInNew = COUNTROWS(FILTER(NewWithMapping, [新包名称]=CurrentNewPackage && [成分]=CurrentComponent)) > 0
             RETURN
                 SWITCH(
                     TRUE(),
                     IsInOld && IsInNew, "KEPT",
                     IsInOld && NOT(IsInNew), "REMOVED",
                     NOT(IsInOld) && IsInNew, "NEW"
                 )
         )
     RETURN
         DISTINCT(SELECTCOLUMNS(StatusCalculated, "旧包名称", [旧包名称], "新包名称", [新包名称], "成分", [成分], "状态", [状态]))
     

   • 生成的PackageComparison表将自动展示每个包对的成分状态，且无冗余行。

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