Power Query中滑动窗口百分位数计算问题求助

问题回顾 #

你有一张已按District、Measurement、Monthdate降序排序的表格，需为距最新Monthdate不超过36个月的每条记录，新增列存储该记录Monthdate往前36个月窗口内Value列的95th百分位数。使用List.Generate时出现We cannot convert Function to List错误，本质是语法或列表生成逻辑有误。

修正方案：用分组+窗口筛选替代List.Generate #

因为表格已按分组字段和日期排序，直接按District+Measurement分组后，在组内筛选时间窗口会更高效，避免List.Generate的语法陷阱。以下是完整M代码：

let
    // 源数据（替换成你的实际数据源）
    Source = YourDataSource,
    // 获取最新日期，计算36个月前的日期作为筛选阈值
    LatestDate = List.Max(Source[Monthdate]),
    ThresholdDate = Date.AddMonths(LatestDate, -36),
    // 筛选出符合时间范围的记录
    FilteredRows = Table.SelectRows(Source, each [Monthdate] >= ThresholdDate),
    // 按District和Measurement分组，保留组内所有行
    Grouped = Table.Group(FilteredRows, {"District", "Measurement"}, 
        {{"GroupData", each _, type table [District=text, Month Cumulative=Int64.Type, Measurement=text, Value=number, Monthdate=date]}}),
    // 对每个分组内的数据计算窗口百分位数
    AddPercentile = Table.TransformColumns(Grouped, {{"GroupData", 
        (table) => 
            let
                // 给分组内的行添加索引（可选，方便调试查看）
                AddIndex = Table.AddIndexColumn(table, "Index", 0, 1),
                // 计算每条记录对应的窗口起始日期（当前Monthdate往前36个月）
                AddWindowStart = Table.AddColumn(AddIndex, "WindowStart", each Date.AddMonths([Monthdate], -36)),
                // 筛选窗口内的Value列表并计算95分位数
                Add95Percentile = Table.AddColumn(AddWindowStart, "95thPercentile", 
                    (row) => 
                        let
                            // 提取当前窗口内的所有Value值
                            WindowValues = Table.SelectRows(AddWindowStart, each [Monthdate] >= row[WindowStart] and [Monthdate] <= row[Monthdate])[Value],
                            // 计算95分位数（Percentile.Inc为包含首尾的计算，Percentile.Exc为排除式，按需选择）
                            Percentile = if List.Count(WindowValues) >= 1 then Percentile.Inc(WindowValues, 0.95) else null
                        in Percentile
                )
            in Add95Percentile
    }}),
    // 展开分组数据，得到最终结果
    ExpandedGroupData = Table.ExpandTableColumn(AddPercentile, "GroupData", {"Month Cumulative", "Value", "Monthdate", "95thPercentile"})
in
    ExpandedGroupData

关于List.Generate报错的原因 #

你之前的错误大概率是List.Generate的返回值没有正确生成列表，比如遗漏了迭代终止条件，或者直接把生成函数传给了百分位数计算函数（而非生成的列表）。举个典型错误示例：

// 错误写法：直接将List.Generate函数传入Percentile.Inc，而非生成的列表
Table.AddColumn(Source, "95thPercentile", each Percentile.Inc(List.Generate(()=>..., ...), 0.95))

即便修正List.Generate的语法，这种写法效率也远低于分组筛选，尤其是数据量较大时。

注意事项 #

• 确保Monthdate是标准日期类型，若原格式为文本，需先通过Date.FromText转换
• 百分位数计算可选Percentile.Inc（包含式）或Percentile.Exc（排除式），根据业务需求选择
• 若窗口内数据量不足（比如第一条记录往前36个月无数据），代码会返回null，可按需调整默认值

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