如何用Power Query M对多数据子集执行线性插值生成新表？

核心逻辑 #

• 按CurveName分组，为每条曲线的原始数据单独生成完整日期序列
• 针对每组独立执行线性插值计算，最后合并所有结果

分步实现代码 #

1. 按曲线分组 #

先将原始数据按CurveName分组，保留每组的日期和利率数据为嵌套表：

let
    源 = 你的数据源,
    // 按CurveName分组，保留每组的原始日期与利率数据
    分组处理 = Table.Group(源, {"CurveName"}, {{"原始曲线数据", each _, type table [BusinessDays=date, InterestRate=number]}})
in
    分组处理

2. 为每组生成完整日期并插值 #

添加自定义列，对每个嵌套表生成完整日期序列并完成线性插值：

let
    源 = 你的数据源,
    分组处理 = Table.Group(源, {"CurveName"}, {{"原始曲线数据", each _, type table [BusinessDays=date, InterestRate=number]}}),
    // 对每组曲线执行插值计算
    添加插值结果 = Table.AddColumn(分组处理, "插值后数据", (当前行) =>
        let
            原始表 = 当前行[原始曲线数据],
            // 获取当前曲线的起止日期
            起始日 = List.Min(原始表[BusinessDays]),
            结束日 = List.Max(原始表[BusinessDays]),
            // 生成起止日之间的所有日期序列
            完整日期列表 = List.Dates(起始日, Duration.Days(结束日 - 起始日) + 1, #duration(1,0,0,0)),
            完整日期表 = Table.FromList(完整日期列表, Splitter.SplitByNothing(), {"BusinessDays"}),
            // 合并原始数据与完整日期表
            合并表 = Table.FullOuterJoin(完整日期表, {"BusinessDays"}, 原始表, {"BusinessDays"}, {"BusinessDays", "原始日期", "InterestRate"}),
            // 清理冗余列并重命名
            清理列 = Table.RenameColumns(Table.RemoveColumns(合并表, {"原始日期"}), {{"BusinessDays", "BusinessDays"}}),
            // 按日期排序
            排序表 = Table.Sort(清理列, {{"BusinessDays", Order.Ascending}}),
            // 执行线性插值（Power Query内置函数，需版本支持）
            线性插值 = Table.Interpolate(sortTable, "InterestRate", InterpolationMethod.Linear)
        in
            线性插值
    ),
    // 展开插值后的嵌套表
    展开结果 = Table.ExpandTableColumn(添加插值结果, "插值后数据", {"BusinessDays", "InterestRate"}, {"BusinessDays", "InterestRate"}),
    // 按曲线名称和日期排序最终结果
    最终排序 = Table.Sort(展开结果, {{"CurveName", Order.Ascending}, {"BusinessDays", Order.Ascending}})
in
    最终排序

3. 兼容低版本的手动插值逻辑 #

如果你的Power Query版本不支持Table.Interpolate，可以替换为手动计算逻辑：

// 替换上述代码中的"线性插值"步骤
// 填充前后非空值
  填充前后值 = Table.FillDown(排序表, {"InterestRate"}),
  填充后前值 = Table.FillUp(填充前后值, {"InterestRate"}),
  // 获取所有非空利率的行索引和对应值
  非空行索引 = List.PositionOf(填充后前值[InterestRate], each _ <> null, Occurrence.All),
  非空利率值 = List.RemoveNulls(填充后前值[InterestRate]),
  非空日期 = List.RemoveNulls(填充后前值[BusinessDays]),
  // 遍历每行计算插值
  手动插值 = Table.FromRecords(List.Transform(Table.ToRecords(填充后前值), (行) =>
      if row[InterestRate] <> null then row
      else
          let
              当前日期 = row[BusinessDays],
              // 找到当前日期前后最近的非空日期索引
              前索引 = List.Last(List.Select(非空行索引, each _ < List.PositionOf(填充后前值[BusinessDays], 当前日期))),
              后索引 = List.First(List.Select(非空行索引, each _ > List.PositionOf(填充后前值[BusinessDays], 当前日期))),
              前日期 = 非空日期{前索引},
              后日期 = 非空日期{后索引},
              前利率 = 非空利率值{前索引},
              后利率 = 非空利率值{后索引},
              // 计算线性插值
              插值结果 = 前利率 + (后利率 - 前利率) * Duration.Days(当前日期 - 前日期) / Duration.Days(后日期 - 前日期)
          in
              Record.ReplaceFields(row, {"InterestRate", 插值结果})
  ))

结果验证 #

处理完成后，每条CurveName会包含从其首个BusinessDay到最后一个BusinessDay的所有日期的插值利率值，可匹配示例中3条曲线总计820行、SOFR曲线376行的输出要求。

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