Power Query为缺失年份插入行生成Power Pivot年度小计的实现方法

操作核心 #

不用手动逐行插入空年份行，通过生成全周期年度节点、自动关联匹配事件的方式补全数据，最终直接支持Power Pivot的年末累计汇总逻辑，完全匹配你要求的输出结构。 #

分步操作 #

1. 预处理原始日志表 #

先对导入的日志表做基础格式校准，避免后续计算出错：

• 将日期列从文本格式转换为标准日期类型，修正类似2015.05.05的文本为可计算的日期值
• 将金额列转换为数字类型，去掉千分位文本，确保后续求和计算正常
• 校验实体列内容，确认所有实体编码无多余空格、无格式差异

2. 自动生成全周期年度节点表 #

覆盖从最早事件年份到指定截止年份的所有12月31日节点，自动和所有实体做配对，无需手动加行。可直接在Power Query高级编辑器中粘贴以下代码，替换对应参数即可：

let
    // 关联实际的日志查询
    原始日志 = 替换为你实际的日志查询名称,
    StartYear = Date.Year(List.Min(原始日志[日期])), // 自动取日志最早事件的年份作为计算起点
    EndYear = 2024, // 替换为你需要的截止年份，示例需求中为2020
    AllEntities = List.Distinct(原始日志[实体]), // 自动提取所有出现过的实体
    // 生成连续年份序列
    YearSequence = { StartYear .. EndYear },
    // 交叉配对所有实体+所有年份的组合
    节点组合 = List.Combine(
        List.Transform(AllEntities, (currentEntity) =>
            List.Transform(YearSequence, (currentYear) => [
                实体 = currentEntity,
                节点年份 = currentYear,
                节点日期 = #date(currentYear, 12, 31)
            ])
        )
    ),
    年度节点表 = Table.FromRecords(节点组合)
in
    年度节点表

3. 按实体、日期范围关联原始日志 #

通过合并查询把原始日志的事件匹配到对应的年度节点上，自动补全无事件的年份行：

• 打开生成的「年度节点表」，点击「主页」选项卡的「合并查询」
• 选择要合并的表为预处理后的「原始日志表」
• 点击合并窗口的「高级」，添加两个匹配条件：
  • 两个表的实体列值相等
  • 原始日志表的日期列 <= 年度节点表的节点日期列
• 连接类型选择「左外部（第一个表的所有行，第二个表的匹配行）」，确认合并
• 展开合并后的原始日志列，选中事件、日期、金额三个字段，取消勾选「使用原始列名作为前缀」

4. 计算累计余额、填充空年份值 #

对匹配后的数据做计算，确保无事件的年份自动沿用上年末的累计金额：

• 按实体、节点日期升序排序所有行
• 新增自定义列累计金额，计算公式为：统计同一实体下、节点日期小于等于当前行节点日期的所有原始金额之和
• 对无事件年份对应的空累计值，执行「填充-向下」操作，自动把上一年的累计值填充到对应空行

5. 调整行结构匹配输出格式 #

• 拆分两类展示行：
  • 保留有原始事件的行：年份列取事件所属年份，事件、日期、金额列沿用原始值
  • 生成年度小计行：年份列写{节点年份} 小计，事件、日期列留空，金额列放对应节点的累计金额
• 最终按节点日期升序、原始事件日期升序排序，即可得到示例中的输出顺序

避坑提示 #

• 不要只按年份相等的条件做匹配，否则没有事件的年份会直接被过滤掉，达不到补全的目的
• 计算累计时不要只统计当年的事件金额，要把截止到节点日期的所有历史事件算入，否则空年份的金额会变成0，不符合2016、2018年保持上年余额的要求
• 所有日期字段必须转成标准日期类型，否则大小比较会出现逻辑错误

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