优化Power Query跨表日期匹配查询：缩短超15分钟运行时长

核心问题分析 #

原代码通过Table.AddColumn结合逐行Table.SelectRows实现匹配，本质是对Table2的每一行都全量扫描Table1，数据量大时会触发O(n*m)的时间复杂度，这是运行耗时超15分钟的根本原因。

优化方案 #

方案一：分组排序+合并匹配（适合中等数据量） #

通过预处理Table1减少重复扫描，再通过Label关联批量处理：

1. 预处理Table1：按Label分组，组内按Period End Date升序排序，确保后续能快速定位符合条件的日期

let
    SortedTable1 = Table.Sort(Table1,{{"Label", Order.Ascending}, {"Period End Date", Order.Ascending}}),
    GroupedTable1 = Table.Group(SortedTable1, {"Label"}, {{"Periods", each _}})
in
    GroupedTable1

2. 合并Table2与预处理后的Table1：通过Label字段关联，避免逐行扫描

let
    MergedTables = Table.NestedJoin(Table2, {"Label"}, GroupedTable1, {"Label"}, "MatchedPeriods", JoinKind.LeftOuter),
    FilledNulls = Table.ReplaceValue(MergedTables, null, #table({"Period End Date", "Month"}, {}), Replacer.ReplaceValue, {"MatchedPeriods"})
in
    FilledNulls

3. 生成Month列：利用已排序的分组数据，直接筛选后取最后一条（即最大的符合条件的日期）

let
    AddedMonthColumn = Table.AddColumn(FilledNulls, "Month", (x) =>
        let
            FilteredPeriods = Table.SelectRows(x[MatchedPeriods], each [Period End Date] <= x[Date]),
            TargetPeriod = if Table.RowCount(FilteredPeriods) > 0 then Table.Last(FilteredPeriods) else null
        in
            if TargetPeriod <> null then TargetPeriod[Month] else null
    ),
    CleanedTable = Table.RemoveColumns(AddedMonthColumn, {"MatchedPeriods"})
in
    CleanedTable

方案二：二分查找（适合超大数据量） #

基于分组排序后的列表，用二分查找替代逐行筛选，将单条记录的匹配复杂度从O(m)降到O(log m)：

let
    // 预处理Table1：按Label分组，提取日期和Month的对应列表
    GroupedTable1 = Table.Group(Table.Sort(Table1,{{"Label", Order.Ascending}, {"Period End Date", Order.Ascending}}), {"Label"}, {
        {"DateList", each [Period End Date]},
        {"MonthList", each [Month]}
    }),
    // 合并表并处理无匹配的情况
    MergedTables = Table.NestedJoin(Table2, {"Label"}, GroupedTable1, {"Label"}, "MatchedData", JoinKind.LeftOuter),
    FilledNulls = Table.ReplaceValue(MergedTables, null, [DateList={}, MonthList={}], Replacer.ReplaceValue, {"MatchedData"}),
    // 二分查找定位对应Month
    AddedMonthColumn = Table.AddColumn(FilledNulls, "Month", (x) =>
        let
            DateList = x[MatchedData][DateList],
            MonthList = x[MatchedData][MonthList],
            // 找到第一个大于目标日期的位置，减一即为最大符合条件的日期索引
            Position = List.BinarySearch(DateList, x[Date], (a,b) => if a <= b then -1 else 1)
        in
            if Position >= 0 then MonthList{Position} else null
    ),
    CleanedTable = Table.RemoveColumns(AddedMonthColumn, {"MatchedData"})
in
    CleanedTable

优化效果说明 #

两种方案均避免了原代码的逐行全表扫描，将整体时间复杂度从O(n*m)降至O(n log m)（二分查找版）或O(n + m log m)（分组排序版），数据量越大，性能提升越显著。

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