- let
- Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
- fx=(t as table)=>
- let
- a=Table.SelectColumns(t,{"上班打卡时间","下班打卡时间"}),
- b=List.FirstN(List.Combine(Table.ToRows(a))&List.Repeat({null},6),6),
- c=b&{List.Sum(List.FirstN(t[工作时间],2)),List.Last(t[工作时间]),List.Sum(t[例外情况])},
- d=List.TransformMany({"1","2","3"},each {"上班","下班"},(x,y)=>y&x)&{"白班(时)","加班(时)","事假(时)"}
- in
- Table.FromRows({c},d),
- Custom1 = Table.Group(Source,{"考勤号码","姓名","日期"},{"n",each fx(_)}),
- Custom2 = Table.ExpandTableColumn(Custom1,"n",Table.ColumnNames(Custom1[n]{0})),
- #"Changed Type" = Table.TransformColumnTypes(Custom2,{{"上班1", type time}, {"下班1", type time}, {"上班2", type time}, {"下班2", type time}, {"上班3", type time}, {"下班3", type time}, {"白班(时)", type number}, {"加班(时)", type number}, {"事假(时)", type number}, {"日期", type date}})
- in
- #"Changed Type"
复制代码
|