Forum Discussion
Solved
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....
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"
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