Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
maximajayaa
New Member

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. 

 

Screenshot 2024-06-15 162035.png

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

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).

  1. 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

collinsg_0-1718445027189.png

...producing,

collinsg_2-1718445273051.png

Hope this helps.

View solution in original post

1 REPLY 1
collinsg
Super User
Super User

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).

  1. 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

collinsg_0-1718445027189.png

...producing,

collinsg_2-1718445273051.png

Hope this helps.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.