Hello,
I have a list of various computer user events i.e. login, logout, shutdown etc. Originally I created a min date/time per date and a max date/time per date and merged the two so I had the min/max to calculate duration. But that doesn't work for split shifts or anyone having to leave for appointments and returning to work.
I have attached a workbook with fake data showing the "pairings" I would like to be using for the duration calculations. How should I approach it to get the results I'd like? All assistance greatly appreciated.
If I understand correctly then, Power Query will do the work
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Access_Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Access_Date", "Employee"}, {{"Login", each List.Min([#"Access Date/Time"]), type datetime}, {"Logout", each List.Max([#"Access Date/Time"]), type datetime}})
in
#"Grouped Rows"
Hello,
Thank you so much for your replies. Anders, I was hoping to keep in PowerQuery but thanks for responding. Alan, unfortunately the formula doesn't take into account when there is a split shift on the same day i.e. it takes the minimum login (first of the day) to maximum logout (last event of the day) but there is a logout/login in between. Any way to change for when multiple of the same events in the day?
See if this works for you
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Pairing] <> 0)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Access Date/Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Login", each if [Action]="Login" then [#"Access Date/Time"] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Logout", each if [Action] = "Logout" then [#"Access Date/Time"] else if [Action] = "Shutdown" then [#"Access Date/Time"] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Logout"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Login] <> null)),
#"Inserted Time Subtraction" = Table.AddColumn(#"Filtered Rows1", "Time In", each [Logout] - [Login], type duration),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time Subtraction",{{"Access_Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Access_Date", "Employee"}, {{"Total Time", each List.Sum([Time In]), type duration}})
in
#"Grouped Rows"
Brilliant, Alan! This did the trick! Thank you so much for sticking with me on this.
