Power Query筛选无匹配时整行空白的修复及自定义输出需求

步骤1：加载数据表并添加索引 #

先加载两张数据表，给Forecast_Tbl添加索引列，确保后续行顺序固定，避免填充上一行值时出错：

let
    Historical = Excel.CurrentWorkbook(){[Name="Historical_Data_Tbl"]}[Content],
    Forecast = Excel.CurrentWorkbook(){[Name="Forecast_Tbl"]}[Content],
    // 添加索引列，固定行顺序
    ForecastWithIndex = Table.AddIndexColumn(Forecast, "RowIndex", 0, 1, Int64.Type)
in
    ForecastWithIndex

步骤2：逐行筛选并计算聚合值 #

给Forecast表新增聚合列，按每行数值范围筛选Historical数据，同时处理无匹配的情况：

let
    // 承接上一步的ForecastWithIndex
    AddAggregation = Table.AddColumn(ForecastWithIndex, "AggregatedData", (row) =>
        let
            // 替换为你实际的范围匹配逻辑
            FilteredHistorical = Table.SelectRows(Historical, (h) =>
                h[RangeStart] <= row[RangeStart] and h[RangeEnd] >= row[RangeEnd]
            ),
            HasMatch = Table.RowCount(FilteredHistorical) > 0,
            // 无匹配时Count设为0，其余统计值留空，Average暂设为null
            Count = if HasMatch then Table.RowCount(FilteredHistorical) else 0,
            MinPrice = if HasMatch then List.Min(FilteredHistorical[Price]) else null,
            MaxPrice = if HasMatch then List.Max(FilteredHistorical[Price]) else null,
            AvgPrice = if HasMatch then List.Average(FilteredHistorical[Price]) else null
        in
            [Count=Count, MinPrice=MinPrice, MaxPrice=MaxPrice, AvgPrice=AvgPrice]
    ),
    // 展开聚合列到单独字段
    ExpandAggregation = Table.ExpandRecordColumn(AddAggregation, "AggregatedData", {"Count", "MinPrice", "MaxPrice", "AvgPrice"})
in
    ExpandAggregation

步骤3：填充Average列的上一行有效值 #

标记需要填充的行，用Table.FillDown将上一行有效值填充到无匹配的行，最后清理临时列：

let
    // 承接上一步的ExpandAggregation
    // 标记需要填充Average的行
    MarkFillRows = Table.AddColumn(ExpandAggregation, "NeedFill", each [AvgPrice] = null),
    // 填充上一行的Average值
    FilledAvg = Table.FillDown(MarkFillRows, {"AvgPrice"}),
    // 清理临时列，保留Forecast前10列和聚合列
    // 若前10列有固定列名，可替换为Table.SelectColumns指定列名
    Cleanup = Table.RemoveColumns(FilledAvg, {"RowIndex", "NeedFill"})
in
    Cleanup

注意事项 #

• 替换代码中RangeStart、RangeEnd为你实际的数值范围字段名
• 若Forecast前10列有固定列名，建议在Cleanup步骤用Table.SelectColumns精确指定，避免列顺序错乱

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