Merge Rows For Specific Date and Person, keeping first Log In and Last Log Out in the merged row

[wiselyman3]

Hi everyone, 

I will do my best to explain this problem I need help with in PowerQuery/PowerBI. I have a table structured like this below: 

Name	Log In	Log Out
Mike	1/27/2025 7:56:00 AM	1/27/2025 10:15:00 AM
Mike	1/27/2025 10:56:00 AM	1/27/2025 12:00:00 PM
Mike	1/27/2025 12:59:00 PM	1/27/2025 4:30:00 PM
Mike	1/28/2025 8:00:00 AM	1/28/2025 4:30:00 PM
Mike	1/29/2025 7:50:00 AM	1/29/2025 2:30:00 PM
Mike	1/29/2025 2:50:00 PM	1/29/2025 5:35:00 PM
Alex	1/27/2025 8:56:00 AM	1/27/2025 11:15:00 AM
Alex	1/27/2025 12:10:00 PM	1/27/2025 5:00:00 PM
Alex	1/28/2025 8:00:00 AM	1/28/2025 3:30:00 PM
Alex	1/28/2025 5:00:00 PM	1/28/2025 7:30:00 PM
Alex	1/28/2025 8:00:00 AM	1/28/2025 4:30:00 PM

I need the desired result to be 1 row for each specific Name and Date combination, with the Log In value being the earliest time for that day and the Log Out value being the latest value for that day. Basically, I need to know when someone made their first log on of the day and last log out of the day, in one row. Desired Results below: 

Name	Log In	Log Out
Mike	1/27/2025 7:56:00 AM	1/27/2025 4:30:00 PM
Mike	1/28/2025 8:00:00 AM	1/28/2025 4:30:00 PM
Mike	1/29/2025 7:50:00 AM	1/29/2025 5:35:00 PM
Alex	1/27/2025 8:56:00 AM	1/27/2025 5:00:00 PM
Alex	1/28/2025 8:00:00 AM	1/28/2025 4:30:00 PM

[danextian · Accepted solution]

Hi @wiselyman3 

First add a date only column.

Use the Group by feature in the query editor.

Here's the full M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdExDoMwDAXQq6DMSNgOboI3DoDEjhgZqvYAPX5T1W1jEUjXnzxH/lkWN11vm2sddhQ6AuImCF8EoBknEyMIsuZrW3DpQhlSCl/5fARJeNALed6LL7v4Po869/NerLDhu59hGlOFkbLZMhbPGRvv28MsEQ9KQdvmzqVSEAqlsC3z585L8Xa7HcvG5nGosD++YH0C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Log In" = _t, #"Log Out" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Log In", type datetime}, {"Log Out", type datetime}}, "en-us"),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Log In]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"Name", "Date"}, {{"First Login", each List.Min([Log In]), type nullable datetime}, {"Last Logout", each List.Max([Log Out]), type nullable datetime}})
in
    #"Grouped Rows"

[wiselyman3]

Thank you! This worked perfectly