Power Query中产品-工作日分组的未来3周递推移动平均模板开发问询

问题背景 #

需针对每个产品按weekday分组，对volume（销量）和sales（销售额）计算递推式3期移动平均：前3期用原始数据计算基础均值，之后每一期的均值用上一期的计算结果替代原始数据中的最早值，滚动计算后续周期的递推均值。

输入表包含多产品、各工作日的date、volume、sales数据。

现有代码修正 #

先修正你现有代码的语法错误（缺失闭合括号），同时简化冗余列名：

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"product", "weekday"}, {{"Index", each Table.AddIndexColumn(_,"row_index", 1,1), type table}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"date", "volume", "sales", "row_index"}, {"date", "volume", "sales", "row_index"})

核心解决方案：分组内递推计算 #

递推式移动平均依赖上一步的计算结果，无法用普通列计算实现，需在product+weekday分组内逐行迭代处理：

完整代码步骤 #

1. 按产品、工作日、日期排序（确保时间顺序正确）

#"Sorted Rows" = Table.Sort(#"你的原始表名",{{"product", Order.Ascending}, {"weekday", Order.Ascending}, {"date", Order.Ascending}})

2. 分组并在组内完成递推均值计算

#"Grouped with Recursive MA" = Table.Group(#"Sorted Rows", {"product", "weekday"}, {
    {"MA_Calculations", each 
        let
            // 提取分组内的原始数据并添加行索引
            source = Table.AddIndexColumn(_, "row_index", 1, 1),
            vol_list = source[volume],
            sales_list = source[sales],
            total_rows = Table.RowCount(source),
            
            // 递推计算销量的移动平均：用List.Accumulate实现迭代
            recursive_vol = List.Accumulate({4..total_rows}, 
                // 初始值：前3行原始数据 + 第3行的基础均值
                List.RemoveLastN(vol_list, total_rows-3) & {List.Average(List.Range(vol_list,0,3))},
                (state, current_row) => 
                    let
                        // 取状态列表的最后3个值（含上一期均值）计算新均值
                        new_avg = List.Average(List.Range(state, List.Count(state)-2, 3))
                    in
                        state & {new_avg}
            ),
            
            // 销售额的递推移动平均，逻辑与销量完全一致
            recursive_sales = List.Accumulate({4..total_rows}, 
                List.RemoveLastN(sales_list, total_rows-3) & {List.Average(List.Range(sales_list,0,3))},
                (state, current_row) => 
                    let
                        new_avg = List.Average(List.Range(state, List.Count(state)-2, 3))
                    in
                        state & {new_avg}
            ),
            
            // 将递推结果合并回原表
            add_vol_ma = Table.FromColumns(Table.ToColumns(source) & {recursive_vol}, Table.ColumnNames(source) & {"recursive_vol_ma"}),
            add_sales_ma = Table.FromColumns(Table.ToColumns(add_vol_ma) & {recursive_sales}, Table.ColumnNames(add_vol_ma) & {"recursive_sales_ma"})
        in
            add_sales_ma, type table}
})

3. 展开分组后的计算结果

#"Expanded MA Results" = Table.ExpandTableColumn(#"Grouped with Recursive MA", "MA_Calculations", {"date", "volume", "sales", "row_index", "recursive_vol_ma", "recursive_sales_ma"})

逻辑说明 #

• 初始阶段：前3行的recursive_vol_ma和recursive_sales_ma为原始数据，第3行的均值是原始3期的平均值（对应你已实现的基础移动平均）。
• 递推阶段：从第4行开始，每一行的均值取前3个最新数值（包含上一期的计算结果）的平均值，实现递推滚动。
• 若仅需计算未来3周的递推值，可将{4..total_rows}替换为{4..6}。

注意事项 #

• 必须保证分组内的数据按date升序排列，否则递推逻辑会失效。
• 若分组内行数不足3行，递推均值会保留原始数据，不会触发报错。

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