Excel PowerQuery：为Log On行匹配下一个Log On前的DateTime

操作步骤 #

假设你的数据源已导入PowerQuery编辑器，表名为Source，按以下步骤操作：

1. 确保数据按时间排序
   先对DateTime字段升序排序，保证交易记录的时间顺序正确：

   = Table.Sort(Source,{{"DateTime", Order.Ascending}})
   

2. 添加索引列
   添加从0开始的索引列，方便后续定位行：

   = Table.AddIndexColumn(上一步结果, "Index", 0, 1, Int64.Type)
   

3. 提取所有Log On的索引列表
   创建自定义步骤，提取所有TransactionType为Log On的行的索引值：

   logOnIndexes = List.Select(Table.Column(上一步结果, "Index"), each 上一步结果{_}[TransactionType] = "Log On")
   

4. 为每条Log On匹配截止时间
   添加自定义列EndDateTime，用以下逻辑计算对应截止时间：

   = Table.AddColumn(上一步结果, "EndDateTime", (row) =>
       if row[TransactionType] = "Log On" then
           let
               currentPos = List.PositionOf(logOnIndexes, row[Index]),
               isLastLogOn = currentPos = List.Count(logOnIndexes) - 1
           in
               if isLastLogOn then
                   // 最后一条Log On，取表中最后一行的DateTime
                   上一步结果{Table.RowCount(上一步结果)-1}[DateTime]
               else
                   // 取下一个Log On的上一行的DateTime
                   上一步结果{logOnIndexes{currentPos + 1} - 1}[DateTime]
       else
           // 非Log On记录可留空或按需处理
           null
   )
   

5. 清理不必要的列（可选）
   若不需要索引列，可删除：

   = Table.RemoveColumns(上一步结果,{"Index"})
   

逻辑说明 #

• 通过logOnIndexes列表记录所有Log On的位置，避免重复判断
• 对每个Log On记录：
  • 若为最后一条Log On，直接取整个表的最后一行DateTime
  • 否则，取下一个Log On的索引减1，定位到对应行并获取DateTime

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