如何用Power Query检测月度表各分类缺失的主表项目？

操作步骤： #

1. 导入数据到Power Query

   • 打开Excel，进入「数据」选项卡，分别导入两个工作簿：
     • 主列表（仅含项目列，确保无重复项），命名为主项目列表
     • 月度文件（含分类、项目列），命名为月度分类项目

2. 生成分类与主项目的完整组合

   • 提取月度表中的唯一分类：选中月度分类项目的分类列，点击「转换」→「分组依据」，分组列选分类，新列名设为临时，操作选「所有行」；选中临时列，点击「转换」→「提取值」，分隔符选逗号，再用列表.Distinct(Text.Split([临时], ","))转成唯一分类列表，最后把列表转成表格，命名为唯一分类
   • 交叉合并唯一分类和主项目列表：选中唯一分类，点击「合并查询」→「合并」，选择主项目列表，合并条件任意选择（目的是生成全量交叉组合）；合并后展开主项目列表的项目列，得到每个分类对应所有34个主项目的完整表，命名为完整分类项目组合

3. 左反连接找出缺失项

   • 选中完整分类项目组合，点击「合并查询」→「合并」，选择月度分类项目，合并条件同时匹配分类和项目两列，连接类型选「左反（所有第一个表的行，在第二个表中没有匹配的行）」
   • 合并完成后，得到的表格就是每个分类下缺失的主项目，直接加载回Excel即可

关键注意事项： #

• 确保主列表的项目无重复值，否则会导致组合数量异常
• 月度表的项目名称要和主列表完全一致（包括大小写、空格、特殊字符），如果有差异，先在Power Query中用Text.Trim([项目])、Text.Lower([项目])统一格式后再操作

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