Power Query按条件修改单元格值：处理设备连续操作时间重复问题

嘿，我之前也踩过Table.Contains的坑——大数据量下全表扫描简直慢到离谱！针对你100多台设备的表格场景，分组+组内逐行对比是最优方案，完全不用全表匹配，处理速度能提升几十倍，给你分享具体实现步骤：

核心思路 #

先按设备分组，每组内按操作开始时间排序，然后只对比当前行的开始时间和上一行的结束时间：如果相等，就给当前开始时间加5分钟。这种方法只在每个设备的小数据范围内处理，避免了全表扫描的低效问题。

具体M代码实现 #

let
    // 替换成你的数据源（比如Excel表格、CSV导入等）
    源 = Excel.CurrentWorkbook(){[Name="设备操作表"]}[Content],
    // 第一步：按设备分组，保留每组的所有行数据
    按设备分组 = Table.Group(源, {"Equipment"}, {{"分组数据", each _, type table [Equipment=text, Rounded Start of operation=datetime, Rounded end of operation=datetime]}}),
    // 第二步：对每个设备的分组数据进行时间调整
    处理每组数据 = Table.TransformColumns(按设备分组, {{"分组数据", (groupTable) => 
        let
            // 组内按开始时间升序排序，确保时间顺序正确
            组内排序 = Table.Sort(groupTable, {{"Rounded Start of operation", Order.Ascending}}),
            // 添加索引列，方便定位上一行数据
            添加索引 = Table.AddIndexColumn(组内排序, "行索引", 0, 1, Int64.Type),
            // 自定义列：判断并调整开始时间
            调整开始时间 = Table.AddColumn(添加索引, "调整后开始时间", each 
                if [行索引] > 0 then
                    let
                        上一行结束时间 = 添加索引{[行索引]-1}[Rounded end of operation]
                    in
                        // 如果当前开始时间等于上一行结束时间，加5分钟
                        if [Rounded Start of operation] = 上一行结束时间 then
                            [Rounded Start of operation] + #duration(0,0,5,0)
                        else
                            [Rounded Start of operation]
                else
                    // 第一行直接保留原开始时间
                    [Rounded Start of operation]
            ),
            // 清理辅助列，恢复表格结构
            移除索引 = Table.RemoveColumns(调整开始时间, {"行索引"}),
            替换原开始时间列 = Table.ReplaceColumns(移除索引, {"Rounded Start of operation", each [调整后开始时间]}, {"Rounded Start of operation", type datetime}),
            移除辅助列 = Table.RemoveColumns(替换原开始时间列, {"调整后开始时间"})
        in
            移除辅助列
    }}),
    // 第三步：展开所有分组数据，恢复完整表格
    展开分组数据 = Table.ExpandTableColumn(处理每组数据, "分组数据", {"Rounded Start of operation", "Rounded end of operation"}),
    // 可选：恢复原始列顺序
    调整列顺序 = Table.ReorderColumns(展开分组数据, {"Equipment", "Rounded Start of operation", "Rounded end of operation"})
in
    调整列顺序

为什么这个方法更快？ #

• 原方法用Table.Contains是全表匹配，时间复杂度是O(n²)，数据量越大越慢；
• 新方法是分组后组内局部处理，时间复杂度主要来自排序的O(n log n)，对于100多台设备的场景，每组数据量有限，处理速度会从小时级压缩到秒级。

额外优化提示 #

如果想让代码更简洁，也可以用List.Generate来生成调整后的时间列表，逻辑和上面一致，只是写法更紧凑：

// 仅展示分组内处理的替代代码
处理每组数据 = Table.TransformColumns(按设备分组, {{"分组数据", (groupTable) => 
    let
        排序后的开始时间 = Table.Sort(groupTable, {{"Rounded Start of operation", Order.Ascending}})[Rounded Start of operation],
        排序后的结束时间 = Table.Sort(groupTable, {{"Rounded Start of operation", Order.Ascending}})[Rounded end of operation],
        // 用List.Generate批量生成调整后的时间
        调整后的时间列表 = List.Generate(
            () => [索引=0, 时间=排序后的开始时间{0}],
            each [索引] < List.Count(排序后的开始时间),
            each [
                索引 = [索引]+1,
                时间 = if 排序后的开始时间{[索引]} = 排序后的结束时间{[索引]-1} then 排序后的开始时间{[索引]} + #duration(0,0,5,0) else 排序后的开始时间{[索引]}
            ],
            each [时间]
        ),
        // 合并回表格
        合并表格 = Table.FromColumns(
            {groupTable[Equipment], 调整后的时间列表, 排序后的结束时间},
            {"Equipment", "Rounded Start of operation", "Rounded end of operation"}
        )
    in
        合并表格
}})

内容的提问来源于stack exchange，提问作者Виктор