Forum Discussion

wiselyman3's avatar
wiselyman3
Helper II
1 year ago
Solved

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

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: 

 

NameLog InLog Out
Mike1/27/2025 7:56:00 AM1/27/2025 10:15:00 AM
Mike1/27/2025 10:56:00 AM1/27/2025 12:00:00 PM
Mike1/27/2025 12:59:00 PM1/27/2025 4:30:00 PM
Mike1/28/2025 8:00:00 AM1/28/2025 4:30:00 PM
Mike1/29/2025 7:50:00 AM1/29/2025 2:30:00 PM
Mike1/29/2025 2:50:00 PM1/29/2025 5:35:00 PM
Alex1/27/2025 8:56:00 AM1/27/2025 11:15:00 AM
Alex1/27/2025 12:10:00 PM1/27/2025 5:00:00 PM
Alex1/28/2025 8:00:00 AM1/28/2025 3:30:00 PM
Alex1/28/2025 5:00:00 PM1/28/2025 7:30:00 PM
Alex1/28/2025 8:00:00 AM1/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: 

NameLog InLog Out
Mike1/27/2025 7:56:00 AM1/27/2025 4:30:00 PM
Mike1/28/2025 8:00:00 AM1/28/2025 4:30:00 PM
Mike1/29/2025 7:50:00 AM1/29/2025 5:35:00 PM
Alex1/27/2025 8:56:00 AM1/27/2025 5:00:00 PM
Alex1/28/2025 8:00:00 AM1/28/2025 4:30:00 PM
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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"

     

     

     

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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"