Punch out/punch in at the same time at different locations

[nobleks7]

I have a situation where I need to call out when an employee is logging out from one location and logging into another location at the same time. If you see sample data set example below, Doe, Jane logged in on row two at 8:50 AM, but logged out at 8:50 am on row on row 4. I need to be able to call out both rows but it's escaping me on how to do so in power query. Someone gave me a great set up to call out one, but I need both entries so I can set up a pivot table to include all entries where someone is doing that (and the file has 48k entries, so not an easy feat). Any help would be greatly appreciated. 

[RossEdwards]

A very simple solution to this problem could be to build a set of unique combined keys and doing a join.  For example if you are looking for records with the same punch datetime for the same person, the key could be a concatination of the employee ID & formatted text of the datetime.  This assumes punch in and punch out datetimes are in different columns or records.

Once you have that, you can do a reference to your query as a new query as 'punch ins' and filter accordingly.  Do another reference query filtered for punch outs.  From 1 reference query do an inner join to the other.  This will now give you a list of records that have a counter part.  Can you mark the other reference query as "Enable Load False" so it doesn't load into your model.

[Natarajan_M · Accepted solution]

Hi @nobleks7 , I hope you are looking for this.

IP:

OP:

Sample Code :

let
    Source = input,

    
    AddInTime = Table.AddColumn(Source, "InTime",
        each Time.FromText(Text.Trim(Text.BeforeDelimiter([Rounded In Out Punches], " - ")))),

    AddOutTime = Table.AddColumn(AddInTime, "OutTime",
        each Time.FromText(Text.Trim(Text.AfterDelimiter([Rounded In Out Punches], " - ")))),

    
    AddFlag = Table.AddColumn(AddOutTime, "Overlap_Flag",
        each
            let
                empID   = [#"EE#"],
                payDate = [#"Pay Date (Worked Date)"],
                inTime  = [InTime],
                outTime = [OutTime],
                loc     = [Location],

                
                outConflict = Table.SelectRows(AddOutTime, each
                    [#"EE#"]                    = empID   and
                    [#"Pay Date (Worked Date)"] = payDate and
                    [Location]                 <> loc     and
                    [InTime]                    = outTime
                ),

                
                inConflict = Table.SelectRows(AddOutTime, each
                    [#"EE#"]                    = empID   and
                    [#"Pay Date (Worked Date)"] = payDate and
                    [Location]                 <> loc     and
                    [OutTime]                   = inTime
                )
            in
                if Table.RowCount(outConflict) > 0 or Table.RowCount(inConflict) > 0
                then "OVERLAP"
                else ""
    ),

    AddWhy = Table.AddColumn(AddFlag, "Overlap_Why",
        each
            let
                empID   = [#"EE#"],
                payDate = [#"Pay Date (Worked Date)"],
                inTime  = [InTime],
                outTime = [OutTime],
                loc     = [Location],

                
                outConflict = Table.SelectRows(AddOutTime, each
                    [#"EE#"]                    = empID   and
                    [#"Pay Date (Worked Date)"] = payDate and
                    [Location]                 <> loc     and
                    [InTime]                    = outTime
                ),

                
                inConflict = Table.SelectRows(AddOutTime, each
                    [#"EE#"]                    = empID   and
                    [#"Pay Date (Worked Date)"] = payDate and
                    [Location]                 <> loc     and
                    [OutTime]                   = inTime
                ),

                outMsg =
                    if Table.RowCount(outConflict) > 0
                    then "OUT " & Time.ToText(outTime, "hh:mm tt") 
                         & " clocks IN at " 
                         & outConflict{0}[Location]
                    else "",

                inMsg =
                    if Table.RowCount(inConflict) > 0
                    then "IN " & Time.ToText(inTime, "hh:mm tt") 
                         & " clocks OUT at " 
                         & inConflict{0}[Location]
                    else "",

                
                combined =
                    if outMsg <> "" and inMsg <> "" then outMsg & " | " & inMsg
                    else if outMsg <> "" then outMsg
                    else if inMsg <> "" then inMsg
                    else ""
            in
                combined
    ),

    RemoveHelpers = Table.RemoveColumns(AddWhy, {"InTime", "OutTime"})

in
    RemoveHelpers

It splits the punch string into separate IN and OUT times, then for each row scans the full dataset to find any other row where the same employee on the same date has a time collision at a different location.

Conflicting rows get flagged as OVERLAP  along with a plain-English explanation of which time matched and which other location was involved. 

Demo: Punch Out.pbix

If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[nobleks7]

I apologize, I omitted that I am working in Excel (2016 which is horrible I know). I'm looking over the PBIX file to see how I can make it work in Excel power query. This is fantastic btw!