如何在Power Query中按姓名和日期合并行提取有效考勤时间

Power Query 实现 #

你用List.Sum聚合时间列报错，核心原因是该函数不适用于时间类型，且你的分组数据中每个时间字段仅存在一个有效非零值（另一个是00:00:00），改用取最大值或过滤无效值的方式即可解决：

1. 替换原Table.Group代码为以下M语言脚本：

= Table.Group(#"Filtered Pablo", {"Name", "DateIn"}, {
    {"AM_In", each List.Max([AM_In_raw]), type time},
    {"AM_Out", each List.Max([AM_Out_raw]), type time},
    {"PM_In", each List.Max([PM_In_raw]), type time},
    {"PM_Out", each List.Max([PM_Out_raw]), type time}
})

2. 逻辑说明：
   • List.Max会自动忽略00:00:00（时间类型的最小值），直接取分组内的有效时间
   • 指定type time确保输出列保持时间格式，避免类型错误

如果数据存在多个非零异常打卡值，可改用过滤后取唯一值的逻辑：

= Table.Group(#"Filtered Pablo", {"Name", "DateIn"}, {
    {"AM_In", each List.First(List.Select([AM_In_raw], (x) => x <> #time(0,0,0))), type time},
    {"AM_Out", each List.First(List.Select([AM_Out_raw], (x) => x <> #time(0,0,0))), type time},
    {"PM_In", each List.First(List.Select([PM_In_raw], (x) => x <> #time(0,0,0))), type time},
    {"PM_Out", each List.First(List.Select([PM_Out_raw], (x) => x <> #time(0,0,0))), type time}
})

SQL 实现 #

如果数据存储在数据库中，直接用GROUP BY结合MAX()函数聚合：

SELECT 
    Name,
    DateIn,
    MAX(AM_In_raw) AS AM_In,
    MAX(AM_Out_raw) AS AM_Out,
    MAX(PM_In_raw) AS PM_In,
    MAX(PM_Out_raw) AS PM_Out
FROM 
    考勤表
GROUP BY 
    Name, DateIn;

逻辑：MAX()会自动选取分组内非零的有效时间值，忽略00:00:00。

Python (Pandas) 实现 #

用Pandas分组后取最大值即可完成合并：

import pandas as pd

# 读取原始考勤数据（假设数据来自Excel）
df = pd.read_excel("原始考勤数据.xlsx")

# 分组聚合并重命名列
result = df.groupby(["Name", "DateIn"], as_index=False).agg({
    "AM_In_raw": "max",
    "AM_Out_raw": "max",
    "PM_In_raw": "max",
    "PM_Out_raw": "max"
}).rename(columns={
    "AM_In_raw": "AM_In",
    "AM_Out_raw": "AM_Out",
    "PM_In_raw": "PM_In",
    "PM_Out_raw": "PM_Out"
})

# 输出或保存结果
print(result)
result.to_excel("合并后考勤数据.xlsx", index=False)

逻辑：Pandas的max()方法对时间类型有效，会自动筛选出分组内的有效非零时间。

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