同一列连续行时间差计算问题（Transform Data标签页代码失效）

先明确核心需求：要给两类记录配对算时间差——带"open"且以"executed by"结尾的记录，对应"open control message"；带"close"且以"executed by"结尾的记录，对应"close control message"。以下是在Power Query（Transform Data标签页）里的可行代码：

1. 给记录打标签，区分类型 #

先给每条目标记录打上专属标签，方便后续配对。假设你的文本列叫[Message]，时间列叫[Timestamp]，在Power Query的高级编辑器里插入这段代码（替换掉之前的步骤名为你实际的步骤名称）：

#"Added Type Tag" = Table.AddColumn(之前的步骤名, "RecordType", each 
    if Text.Contains([Message], "open") and Text.EndsWith([Message], "executed by") then "Open_Exec"
    else if [Message] = "open control message" then "Open_Msg"
    else if Text.Contains([Message], "close") and Text.EndsWith([Message], "executed by") then "Close_Exec"
    else if [Message] = "close control message" then "Close_Msg"
    else null
)

2. 分组配对并计算时间差 #

过滤掉无关记录，按Open/Close分组，把对应的Msg和Exec行配对后算时间差：

#"Filtered Relevant Rows" = Table.SelectRows(#"Added Type Tag", each [RecordType] <> null),
#"Grouped by Type" = Table.Group(#"Filtered Relevant Rows", {"TypeGroup"}, {
    {"Rows", each _, type table},
    {"SortedTimes", each List.Sort([Timestamp])}
}, (row) => Text.Start(row[RecordType], 4)), // 用Open/Close作为分组依据
#"Calculated Time Diff" = Table.AddColumn(#"Grouped by Type", "TimeDiff_Seconds", each 
    let
        times = [SortedTimes],
        // 按顺序配对相邻的Msg和Exec，计算时间差（默认Msg在前，Exec在后）
        diffList = List.Generate(
            () => [index=0, diff=null],
            each [index] < List.Count(times)-1,
            each [index=[index]+2, diff=Duration.TotalSeconds(times{[index]+1} - times{[index]})],
            each [diff]
        )
    in
        diffList
),
#"Expanded Diff" = Table.ExpandListColumn(#"Calculated Time Diff", "TimeDiff_Seconds"),
#"Expanded Rows" = Table.ExpandTableColumn(#"Expanded Diff", "Rows", {"Timestamp", "Message", "RecordType"}, {"Timestamp", "Message", "RecordType"})

3. 可选：格式化时间差 #

如果要把秒数转成时分秒格式，加这一步：

#"Formatted Time Diff" = Table.AddColumn(#"Expanded Rows", "TimeDiff_Formatted", each 
    if [TimeDiff_Seconds] <> null then Duration.ToText(Duration.From([TimeDiff_Seconds]), "hh:mm:ss")
    else null
)

注意事项 #

• 先确认[Timestamp]是datetime类型，如果是文本，先转格式：Table.TransformColumns(之前步骤, {"Timestamp", each DateTime.From(_)})
• 如果你的记录顺序是Exec在前、Msg在后，把times{[index]+1} - times{[index]}改成times{[index]} - times{[index]+1}
• 要是有不配对的孤立记录，加一步Table.SelectRows(#"Expanded Diff", each [TimeDiff_Seconds] <> null)过滤掉空值

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