基于点火状态的叉车工作时长计算及边界条件处理技术需求

我看你已经搞定了基础的工作时长计算，但碰到了跨天和当日未闭合ON记录的棘手情况——放心，咱们调整下Power Query代码就能完美解决：当设备当日最后一条是ON时，把时长算到当日午夜，后续的OFF记录则计入次日的工作时长。

完整修改后的代码 #

let
    Source = Excel.Workbook(File.Contents("C:\Users\avyas2\Desktop\DataToTest.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DEVICEID", Int64.Type}, {"DATETIME", type datetime}, {"IGNITIONSTATUS", type text}}),
    // 按设备+时间排序，保证记录顺序正确，配对不会出错
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DEVICEID", Order.Ascending}, {"DATETIME", Order.Ascending}}),
    // 给每条ON记录找对应的下一条OFF（跨天也能匹配）
    #"Added Next Status" = Table.AddColumn(#"Sorted Rows", "NextStatus", (current) => 
        let
            sameDeviceLater = Table.SelectRows(#"Sorted Rows", each [DEVICEID] = current[DEVICEID] and [DATETIME] > current[DATETIME]),
            nextOffRecord = Table.SelectRows(sameDeviceLater, each [IGNITIONSTATUS] = "OFF"){0}?
        in nextOffRecord),
    // 只保留ON记录，因为我们要基于ON来计算时长
    #"Filter ON Rows" = Table.SelectRows(#"Added Next Status", each [IGNITIONSTATUS] = "ON"),
    // 处理无后续OFF的情况：用当日午夜作为结束时间
    #"Extend ON-OFF Pairs" = Table.TransformColumns(#"Filter ON Rows", {
        {"DATETIME", each _, type datetime},
        {"NextStatus", (next) => if next = null then 
            DateTime.From(DateTime.Date(_) + #duration(1,0,0,0)) // 当日午夜（次日0点）
        else next[DATETIME], type datetime}
    }),
    // 把跨天的时间区间拆分成单日记录
    #"Split Cross-Day Intervals" = Table.ExpandListColumn(Table.AddColumn(#"Extend ON-OFF Pairs", "DailyIntervals", (row) => 
        let
            startDt = row[DATETIME],
            endDt = row[NextStatus],
            startDate = DateTime.Date(startDt),
            endDate = DateTime.Date(endDt),
            sameDay = startDate = endDate,
            // 跨天就拆成两段：当天到午夜，次日从0点到OFF时间
            intervals = if sameDay then 
                {{startDt, endDt, startDate}}
            else 
                {{startDt, DateTime.From(startDate + #duration(1,0,0,0)), startDate},
                 {DateTime.From(endDate), endDt, endDate}}
        in intervals), "DailyIntervals"),
    // 拆分区间字段到单独列
    #"Split Interval Column" = Table.SplitColumn(#"Split Cross-Day Intervals", "DailyIntervals", (x) => {x{0}, x{1}, x{2}}, {"StartDateTime", "EndDateTime", "Date"}),
    // 计算当日工作时长
    #"Calculate Daily Hours" = Table.AddColumn(#"Split Interval Column", "WorkingHrs", each Duration.TotalHours([EndDateTime] - [StartDateTime]), type number),
    // 整理最终需要的列
    #"Final Cleanup" = Table.SelectColumns(#"Calculate Daily Hours", {"DEVICEID", "Date", "WorkingHrs"})
in
    #"Final Cleanup"

关键调整点解释 #

1. 排序与配对逻辑：
   先按设备和时间排序，确保每条ON记录能精准找到后续的第一条OFF记录——哪怕这个OFF是第二天的。如果某条ON之后没有OFF（设备一直处于ON状态到当日结束），就自动用**当日午夜（次日0点）**作为临时结束时间。

2. 跨天区间拆分：
   当ON-OFF区间跨天时，我们把它拆成两个独立的单日区间：

   • 第一段：从ON时间到当日午夜，归属到ON所在的日期
   • 第二段：从次日0点到OFF时间，归属到OFF所在的日期
     这样就完美实现了你要求的“跨天时长分日期统计”。

3. 最终计算：
   对拆分后的每个单日区间计算时长，最后只保留设备ID、日期、工作时长三个核心字段，结果更清晰。

用你的测试数据集跑一遍，比如设备2在10/28的最后一条ON记录，会被计算为从14:01:44到24:00的时长；而10/29的OFF记录则对应10/29当天0:00到14:01:44的时长，完全符合需求。

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