Power Query员工累计工时求和代码故障排查求助

多次尝试后，我编写的Power Query代码仍无法实现按员工维度的工时累计汇总，恳请协助排查代码错误。以下是我的代码：

let
    Source = let
       EmployeeName = {"EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2"},
    DayOfWeek = {"Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday"},
    Date = {"01/03/2024", "02/03/2024", "03/03/2024", "04/03/2024", "05/03/2024", "06/03/2024", "07/03/2024", "01/03/2024", "02/03/2024", "03/03/2024", "04/03/2024", "05/03/2024", "06/03/2024", "07/03/2024"},
    IN = {null, null, "09:05", "08:20", "09:30", null, "09:35", null, null, "09:15", "08:40", "09:50", "10:20", "09:55"},
    OUT = {null, null, "19:25", "18:30", "14:50", null, "16:45", null, null, "16:38", "18:10", "15:30", "12:50", "14:15"},
    HoursWorked = {"00:00", "00:00", "10:20", "10:10", "05:20", "07:10", "00:00", "00:00", "00:00", "07:23", "09:30", "05:40", "02:30", "04:20"},
    Status = {"WeekEnd", "WeekEnd", null, null, null, "Holiday", null, "WeekEnd", "WeekEnd", null, null, null, null, null},

       Table = Table.FromColumns({EmployeeName, DayOfWeek, Date, IN, OUT, HoursWorked, Status}, type table [EmployeeName=text, DayOfWeek=text, Date=text, IN=text, OUT=text, HoursWorked=text, Status=text])
in
    Table,
    GroupedRows = Table.Group(Source, {"EmployeeName"}, {{"Emp", each _, type table [EmployeeName=text, DayOfWeek=text, Date=text, IN=text, OUT=text, HoursWorked=text, Status=text]}}),
    NumToTime = (TimeCol as number) as text =>

let
    
hours = Number.RoundDown(24 * TimeCol),

minutes = Number.Round(60 * Number.Round((24 * TimeCol - hours) * 10000) / 10000),

 FrmtTime = Text.Combine({Text.From(hours), ":", 
                         Text.PadStart(Text.From(minutes), 2, "0")})
in
    FrmtTime,

    AddIndexToTable = Table.AddColumn(GroupedRows, "iEmp", each Table.AddIndexColumn([Emp], "Index", 1, 1, Int64.Type)),
  
    AddNewColumn = Table.AddColumn(AddIndexToTable, "nEmp", each Table.AddColumn([iEmp], "nHours", each [Hours Worked])),

    AddedCustom = Table.AddColumn(AddNewColumn, "TmpSum", each Table.AddColumn( [nEmp] , "Running Total", each List.Sum( List.Range( [nEmp], [nHours], 0, [Index] )))),

    Custom2 = Table.AddColumn(AddedCustom, "FinalTmpSum", each Table.AddColumn([TmpSum], "Running Total (Hours:Minutes)", each NumToTime([TmpSum][Running Total])))
in
    Custom2

代码错误分析 #

• 字段名拼写错误：AddNewColumn步骤中使用[Hours Worked]，但原表字段是HoursWorked（无空格），导致无法正确提取工时数据。
• List.Range参数错误：List.Range的正确参数顺序是List.Range(列表, 起始位置, 数量)，原代码中参数顺序完全混乱，无法正确截取需要累计的工时范围。
• 嵌套表引用错误：计算累计和时，直接引用[nEmp]整个表而非nHours列的数值列表，且求和逻辑错误，没有针对当前行之前的工时进行累计。
• 数据类型未转换：HoursWorked是文本格式（如"10:20"），直接求和会导致错误，需先转换为数值类型的小时数。

修正后的代码 #

let
    Source = let
        EmployeeName = {"EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-1", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2", "EMP-2"},
        DayOfWeek = {"Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday"},
        Date = {"01/03/2024", "02/03/2024", "03/03/2024", "04/03/2024", "05/03/2024", "06/03/2024", "07/03/2024", "01/03/2024", "02/03/2024", "03/03/2024", "04/03/2024", "05/03/2024", "06/03/2024", "07/03/2024"},
        IN = {null, null, "09:05", "08:20", "09:30", null, "09:35", null, null, "09:15", "08:40", "09:50", "10:20", "09:55"},
        OUT = {null, null, "19:25", "18:30", "14:50", null, "16:45", null, null, "16:38", "18:10", "15:30", "12:50", "14:15"},
        HoursWorked = {"00:00", "00:00", "10:20", "10:10", "05:20", "07:10", "00:00", "00:00", "00:00", "07:23", "09:30", "05:40", "02:30", "04:20"},
        Status = {"WeekEnd", "WeekEnd", null, null, null, "Holiday", null, "WeekEnd", "WeekEnd", null, null, null, null, null},
        Table = Table.FromColumns({EmployeeName, DayOfWeek, Date, IN, OUT, HoursWorked, Status}, type table [EmployeeName=text, DayOfWeek=text, Date=text, IN=text, OUT=text, HoursWorked=text, Status=text])
    in
        Table,
    // 将HoursWorked文本转换为数值类型的小时数
    ConvertHours = Table.AddColumn(Source, "HoursNumeric", each 
        let
            TimeParts = Text.Split([HoursWorked], ":"),
            Hours = Number.From(TimeParts{0}),
            Minutes = Number.From(TimeParts{1})
        in
            Hours + Minutes/60
    ),
    // 按员工分组
    GroupedRows = Table.Group(ConvertHours, {"EmployeeName"}, {{"EmpData", each _, type table [EmployeeName=text, DayOfWeek=text, Date=text, IN=text, OUT=text, HoursWorked=text, Status=text, HoursNumeric=number]}}),
    // 定义时分转换函数
    NumToTime = (TimeCol as number) as text =>
        let
            hours = Number.RoundDown(TimeCol),
            minutes = Number.Round((TimeCol - hours)*60)
        in
            Text.Combine({Text.From(hours), ":", Text.PadStart(Text.From(minutes), 2, "0")}),
    // 为每个员工的分组表添加索引并计算累计工时
    AddRunningTotal = Table.AddColumn(GroupedRows, "EmpWithRunningTotal", each 
        let
            IndexedTable = Table.AddIndexColumn([EmpData], "RowIndex", 1, 1),
            RunningTotal = Table.AddColumn(IndexedTable, "RunningTotalHours", each 
                List.Sum(Table.FirstN(IndexedTable, [RowIndex])[HoursNumeric])
            ),
            ConvertToTimeFormat = Table.AddColumn(RunningTotal, "Running Total (Hours:Minutes)", each NumToTime([RunningTotalHours]))
        in
            ConvertToTimeFormat
    ),
    // 展开最终结果表
    FinalResult = Table.ExpandTableColumn(AddRunningTotal, "EmpWithRunningTotal", {"DayOfWeek", "Date", "IN", "OUT", "HoursWorked", "Status", "RunningTotalHours", "Running Total (Hours:Minutes)"})
in
    FinalResult

修正说明 #

1. 新增ConvertHours步骤，将HoursWorked的文本格式（如"10:20"）转换为数值类型的小时数（如10.333），方便后续求和。
2. 调整分组后的数据处理逻辑，使用Table.FirstN获取当前行及之前的所有行，再对HoursNumeric列求和得到累计工时。
3. 简化NumToTime函数的计算逻辑，避免不必要的复杂运算。
4. 最后展开嵌套表，得到清晰的按员工维度的累计工时结果。

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