如何用Excel Query基于日期时间差生成间隔1分钟的明细行

操作步骤： #

1. 选中数据区域，点击数据选项卡 → 从表格/区域，将数据导入Power Query编辑器（勾选"我的表格有标题"）。
2. 转换日期格式：选中Start Date和End Date列，右键 → 更改类型 → 日期/时间，确保这两列被识别为日期时间类型。
3. 生成时间序列列：点击添加列 → 自定义列，输入公式：

   List.Dates([Start Date], Duration.TotalMinutes([End Date]-[Start Date]), #duration(0,0,1,0))
   

   该公式会从起始时间开始，生成总分钟数等于时间差的序列，每步间隔1分钟。
4. 展开序列为新行：点击自定义列右侧的展开按钮，选择展开到新行。
5. 调整起止时间列：
   • 将展开后的自定义列重命名为New Start Date。
   • 添加新自定义列作为结束时间，公式为：

     [New Start Date] + #duration(0,0,1,0)
     

   • 删除原有Start Date和End Date列，将New Start Date重命名为Start Date，新自定义列重命名为End Date。
6. 点击关闭并上载，将处理后的数据导出回Excel。

完整M代码示例 #

若要直接替换Power Query编辑器中的代码（假设原始表名为Table1），可使用以下内容：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    更改类型 = Table.TransformColumnTypes(源,{{"UserId", type text}, {"Status", type text}, {"Duration", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}}),
    添加时间序列 = Table.AddColumn(更改类型, "时间序列", each List.Dates([Start Date], Duration.TotalMinutes([End Date]-[Start Date]), #duration(0,0,1,0))),
    展开序列 = Table.ExpandListColumn(添加时间序列, "时间序列"),
    添加结束时间 = Table.AddColumn(展开序列, "新End Date", each [时间序列] + #duration(0,0,1,0)),
    重命名列 = Table.RenameColumns(添加结束时间,{{"时间序列", "Start Date"}, {"新End Date", "End Date"}}),
    调整列顺序 = Table.ReorderColumns(重命名列,{"UserId", "Status", "Duration", "Start Date", "End Date"})
in
    调整列顺序

效果说明 #

处理后的数据会将原始每行的时间区间拆分为多个1分钟间隔的行，每行起始时间为上一行的结束时间，最终行的结束时间与原始数据一致，完全匹配期望输出格式。

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