Power Query中如何实现跨年度同公司同产品利润同口径对比

核心逻辑：先补全每家公司各年度的全产品维度，消除产品上新、退市带来的维度差，再做跨年度利润匹配，全程可通过Power Query界面操作完成，无需复杂自定义代码。

具体操作步骤 #

• 1. 数据预处理
     将原始销售数据导入Power Query编辑器，检查字段类型：Company、Product设为文本类型，Profit设为数值类型，Year设为整数类型。
• 2. 构建全量维度底表
     这一步是为了覆盖所有公司、年度、产品的组合，避免漏算退市/未上市产品：
  • 分别选中Company、Year、Product三列，依次执行「删除重复项」操作，将得到的三个单列表分别创建为新查询，命名为公司维度、年度维度、产品维度
  • 给三个维度查询都添加自定义列，列名填关联键，列值固定为1
  • 以关联键为匹配条件，对三个维度查询做左连接合并，生成所有「公司-年度-产品」的笛卡尔积组合，将该查询命名为全量底表
• 3. 匹配当年实际利润
     选中全量底表执行合并查询，匹配条件为两表的Company、Product、Year三列完全相等，关联类型选左外部，连接原始销售数据表；展开合并结果后仅保留Profit列，将列中空值全部填充为0，并重命名为当年利润。
• 4. 匹配上一年度同产品利润
  • 给全量底表添加自定义列，列名设为上一年年份，公式为[Year] - 1
  • 再次执行合并查询，左表为当前全量底表，右表选择全量底表自身，匹配条件设置为：左表Company=右表Company、左表Product=右表Product、左表上一年年份=右表Year，关联类型选左外部
  • 展开右表的当年利润字段，重命名为上一年同产品利润，空值全部填充为0
• 5. 结果清洗输出
     筛选掉当年利润和上一年同产品利润同时为0的行（即对应公司从未销售过该产品的无效记录），调整列顺序后即可加载使用。

输出效果示例 #

处理后公司X的2020-2022年数据结构如下：

Company Year Product  当年利润  上一年同产品利润
X       2020 Soap     20       0
X       2020 Shampoo  30       0
X       2021 Noodles  25       0
X       2021 Shampoo  40       30
X       2021 Soap     0        20
X       2022 Coffee   60       0
X       2022 Shampoo  34       40
X       2022 Noodles  0        25

如果需要保留null值而非0填充，在空值处理步骤跳过填充0的操作即可。

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