如何在PowerQuery中创建累加至2040年的逐年补全数据集

问题分析 #

现有分组累加代码仅能处理已有数据的年份，无法生成过往缺失年份及2040年前的空白行，需通过构建全量年份维度框架+左连接填充+分组累加的方式实现需求。

实现步骤 #

• 提取所有不重复的维度组合（Company、Region、Country、LoR、Status）
• 生成从数据最早年份到2040的连续年份列表
• 交叉合并维度组合与年份列表，构建全量行结构框架
• 左连接原始数据到框架表，将缺失的Capacity值填充为0
• 按维度分组，对每个分组内的年份按顺序计算累加容量
• 识别每个分组最后一个有新增数据的年份，将后续年份的累加值固定为该最终值

完整M代码 #

替换你现有Table.Group步骤，使用以下代码：

let
    Source = #"Some Previous steps",
    // 提取唯一维度组合
    UniqueDimensions = Table.Distinct(Source, {"Company", "Region", "Country", "LoR", "Status"}),
    // 生成完整年份序列（自动取数据最早年份到2040）
    MinYear = List.Min(Source[Year]),
    MaxTargetYear = 2040,
    YearList = List.Numbers(MinYear, MaxTargetYear - MinYear + 1),
    YearTable = Table.FromList(YearList, Splitter.SplitByNothing(), {"Year"}),
    // 交叉组合维度与年份，构建全量框架
    FullFramework = Table.CrossJoin(UniqueDimensions, YearTable),
    // 左连接原始数据，填充缺失Capacity为0
    MergedData = Table.NestedJoin(FullFramework, {"Company", "Region", "Year", "Country", "LoR", "Status"}, Source, {"Company", "Region", "Year", "Country", "LoR", "Status"}, "OriginalData", JoinKind.LeftOuter),
    ExpandedData = Table.ExpandTableColumn(MergedData, "OriginalData", {"Capacity [kt]"}, {"Capacity [kt]"}),
    FilledMissing = Table.ReplaceValue(ExpandedData, null, 0, Replacer.ReplaceValue, {"Capacity [kt]"}),
    // 按维度分组并按年份排序
    Grouped = Table.Group(FilledMissing, {"Company", "Region", "Country", "LoR", "Status"}, {
        {"AllData", each Table.Sort(_, {"Year", Order.Ascending}), type table}
    }),
    // 计算累加容量
    CalculatedAccumulated = Table.TransformColumns(Grouped, {
        {"AllData", (tbl) => 
            let
                AddedIndex = Table.AddIndexColumn(tbl, "Index", 0, 1),
                Accumulated = Table.AddColumn(AddedIndex, "Accumulated Capacity [kt]", each List.Sum(List.FirstN(tbl[#"Capacity [kt]"], [Index]+1)))
            in
                Accumulated
        }
    }),
    // 固定无新增数据年份的累加值
    FixedFinalValues = Table.TransformColumns(CalculatedAccumulated, {
        {"AllData", (tbl) => 
            let
                LastNonZeroRow = List.Last(Table.SelectRows(tbl, each [#"Capacity [kt]"] > 0)),
                FinalAccumulatedValue = if LastNonZeroRow <> null then LastNonZeroRow[#"Accumulated Capacity [kt]"] else 0,
                UpdatedRows = Table.ReplaceValue(tbl, each [#"Accumulated Capacity [kt]"], 
                    (row) => if row[Year] > LastNonZeroRow[Year] then FinalAccumulatedValue else row[#"Accumulated Capacity [kt]"],
                    Replacer.ReplaceValue, {"Accumulated Capacity [kt]"}
                )
            in
                UpdatedRows
        }
    }),
    // 展开并清理数据
    ExpandedFinal = Table.ExpandTableColumn(FixedFinalValues, "AllData", {"Year", "Capacity [kt]", "Accumulated Capacity [kt]"}),
    CleanedTable = Table.RemoveColumns(ExpandedFinal, {"Index"})
in
    CleanedTable

代码说明 #

• 自动识别数据起始年份，无需手动指定，适配不同数据源
• 交叉连接确保所有维度组合的每个年份都生成对应行，满足补全需求
• 通过左连接填充缺失值为0，保证累加计算的连续性
• 自动锁定最后一次新增数据的累加值，后续年份保持该值不变，符合规则3、4、5的要求

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