Forum Discussion
Transform data to sum working hours
Hi,
I've got a table
| Employee_id | Date | Time | Type |
| 630 | 02.01.2024 00:00:00 | 09:45:50 | IN |
| 630 | 02.01.2024 00:00:00 | 17:02:40 | OUT |
| 630 | 13.02.2024 00:00:00 | 16:06:30 | OUT |
| 630 | 17.09.2024 00:00:00 | 06:37:50 | IN |
| 630 | 17.09.2024 00:00:00 | 08:07:30 | OUT |
| 630 | 17.09.2024 00:00:00 | 08:21:00 | IN |
| 630 | 17.09.2024 00:00:00 | 16:20:00 |
|
For each employee_id and each date I need to find IN and OUT pairs.
If there's pair IN and OUT return IN and OUT in pair-rows. In my example theres going to be one row for 02.01.2024 but two rows for 17.09.2024 and it means that employee left company in middle of the day.
Theres also case that he forgot to Clock in or clock out. Then I need to add proper description to new column.
I've tried to do write it myself but failed.
What I want to achieve is basically something like that.
| Employee_id | Date | IN | OUT | DESCRIPTION |
| 630 | 02.01.2024 00:00:00 | 09:45:50 | 17:02:40 | |
| 630 | 13.02.2024 00:00:00 | null | 16:06:30 | NOT CLOCKED IN |
| 630 | 17.09.2024 00:00:00 | 06:37:50 | 08:07:30 | |
| 630 | 17.09.2024 00:00:00 | 08:21:00 | 16:20:00 |
IT was really interesting question,
Find the solution as bellow
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 or (not (Source[Type]{[Index]}="OUT" and Source[Type]{[Index]-1}="IN")) then [Index] else [Index]-1), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"values", each _}}), #"Uppercased Text" = Table.TransformColumns(#"Grouped Rows",{{"values", each Table.RemoveColumns(_,{"Index","Custom"})}}), Custom1 = Table.TransformColumns( #"Uppercased Text",{{"values", each Table.Pivot(_,{"IN","OUT"},"Type","Time")}}), Custom2 = Table.TransformColumns( Custom1,{{"values", each Table.AddColumn(_,"Description", (x)=> if x[IN]=null then "NOT CLOCKED IN" else if x[OUT]=null then "NOT CLOCKED OUT" else "")}}), #"Removed Columns" = Table.RemoveColumns(Custom2,{"Custom"}), #"Expanded values" = Table.ExpandTableColumn(#"Removed Columns", "values", {"Employee_id", "Date", "IN", "OUT", "Description"}, {"Employee_id", "Date", "IN", "OUT", "Description"}) in #"Expanded values"
6 Replies
IT was really interesting question,
Find the solution as bellow
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 or (not (Source[Type]{[Index]}="OUT" and Source[Type]{[Index]-1}="IN")) then [Index] else [Index]-1), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"values", each _}}), #"Uppercased Text" = Table.TransformColumns(#"Grouped Rows",{{"values", each Table.RemoveColumns(_,{"Index","Custom"})}}), Custom1 = Table.TransformColumns( #"Uppercased Text",{{"values", each Table.Pivot(_,{"IN","OUT"},"Type","Time")}}), Custom2 = Table.TransformColumns( Custom1,{{"values", each Table.AddColumn(_,"Description", (x)=> if x[IN]=null then "NOT CLOCKED IN" else if x[OUT]=null then "NOT CLOCKED OUT" else "")}}), #"Removed Columns" = Table.RemoveColumns(Custom2,{"Custom"}), #"Expanded values" = Table.ExpandTableColumn(#"Removed Columns", "values", {"Employee_id", "Date", "IN", "OUT", "Description"}, {"Employee_id", "Date", "IN", "OUT", "Description"}) in #"Expanded values"Worked like a charm. Thank you!
You are welcome, and thanks for sharing your lovely question.
Yeah, what could possibly go wrong? What if you have two IN events ?
Your last line has no entry for Description
How do you know that it should by an OUT?
What if he negelected to log OUT earlier in the day and this is really an IN and he's either still there or forgot to log OUT for the second time that day?
let idx = Table.AddIndexColumn(your_table, "idx"), gkl = Table.Group( idx, {"Employee_id", "Date", "Type", "idx"}, {"x", (x) => Record.FromList(x[Time], x[Type]) & [DESCRIPTION = if Table.RowCount(x) = 2 then null else "NOT CLOCKED " & List.Difference({"IN", "OUT"}, x[Type]){0}]}, GroupKind.Local, (s, c) => Number.From( List.AnyTrue( { s[Employee_id] <> c[Employee_id], s[Date] <> c[Date], c[Type] = "IN", s[Type] = "IN" and c[idx] - s[idx] > 1, s[Type] = c[Type] } ) ) ), xpand = Table.ExpandRecordColumn(gkl, "x", {"IN", "OUT", "DESCRIPTION"})[[Employee_id], [Date], [IN], [OUT], [DESCRIPTION]] in xpand
