Forum Discussion
Calculate MIN and MAX Time using Power Query
I have a list of the time when an employee checked in and check out from office. However, this time is inputted in the same column. I am having a difficulty to create another two columns to represent their Checked in and Checked out time. I used the MIN and MAX function in power query but it did not work.
Thank you in advance.
Good day maximajayaa,
If you have three columns: employee ID, date and time then this approach will work (provided each employee does not have more than one sign in or more than one sign out during a day).
- Group by employee ID and date, creating an aggregated column "In" aggregating using the min time and an aggreated column "Out" aggregating using the max time.
Here is sample code...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAwDAPRXVgbMC3lyy7JGIL2XyOIyxguDw8XgQsFzSu3arTlC4orsgTugXa1o9N4uaxNLhO90/MnniIn5KIh8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type date}, {"Time", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee", "Date"}, {{"In", each List.Min([Time]), type nullable time}, {"Out", each List.Max([Time]), type nullable time}})
in
#"Grouped Rows"...and here is how to do this via the GUI
...producing,
Hope this helps.
1 Reply
Good day maximajayaa,
If you have three columns: employee ID, date and time then this approach will work (provided each employee does not have more than one sign in or more than one sign out during a day).
- Group by employee ID and date, creating an aggregated column "In" aggregating using the min time and an aggreated column "Out" aggregating using the max time.
Here is sample code...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAwDAPRXVgbMC3lyy7JGIL2XyOIyxguDw8XgQsFzSu3arTlC4orsgTugXa1o9N4uaxNLhO90/MnniIn5KIh8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t, Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type date}, {"Time", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee", "Date"}, {{"In", each List.Min([Time]), type nullable time}, {"Out", each List.Max([Time]), type nullable time}})
in
#"Grouped Rows"...and here is how to do this via the GUI
...producing,
Hope this helps.