Help calculating work hours duration based on status in and status out entries in Power Query

[TiasHooijenga]

I've been struggling getting the correct amount of minutes/duration of employee work day.

We have a system in place where employees update their status based on if they're working and where.

When working you create an entry with Status In and Comment is the location where you're working.

The system allows for multiple IN and OUT entries in a row. Which makes it too problematic for me:

Cannot seem to find a way to match the correct In/Out pairs to calculate duration between them.

Working in Room1, moving to Room2 is another IN entry with different Comment.

going out for lunch is Status Out and free to choose Comment
back in work is Status In and Comment is working location

After I group the data by EmployeeID and Date is this a realistic simplified sample of the data.

EmployeeID	Entry created at	Status	Comment
100	2-9-2019 09:05:00	In	OfficeRoom1
100	2-9-2019 12:57:00	In	OfficeRoom2
100	2-9-2019 13:36:00	Out	Lunch
100	2-9-2019 14:29:00	In	OfficeRoom2
100	2-9-2019 15:06:00	In	OfficeRoom2
100	2-9-2019 16:29:00	In	OfficeRoom1
100	2-9-2019 16:45:00	Out	Out
100	2-9-2019 17:43:00	In	Home
100	2-9-2019 17:43:01	In	Home
100	2-9-2019 18:22:00	Out	Out
100	2-9-2019 22:22:00	Out	Holidayyyzz!
100	2-9-2019 22:24:14	Out	Holiday till 12 september ☀️

In this example the duration should be counted:

starting at 09:05:00 till lunch 13:36:00 
+
and back from lunch at 14:29:00 till out at 16:45:00
+
in again at 17:43:00 till 18:22:00 out

The double Ins and double Outs give me trouble!

I've tried all the examples and measures I could find online:

ATTEMPT 1: cant find source of this solution anymore
After splitting Created at in Date and Time columns do some magic/matching in the Grouping step:

= Table.Group(#"Sort chronologisch", {"Employee", "Date"}, 
    {
        {"ClockTimes", 
            each
            let
                times = _[[Time], [Status]],
                ins = List.Sort(Table.SelectRows(times, each [Status] = "In")[Time]),
                outs = List.Sort(Table.SelectRows(times, each [Status] = "Out")[Time]),
                zip = List.Select(List.Zip({ins, outs}), each _{0} <> null and _{1} <> null )
            in
                zip
        , type {{time}}
        }
        
    }
    
            )

 Creates a row per employee per day with a column which contains a list of lists with clocktimes.

= Table.SplitColumn(#"Remove Empty", "PunchTimes", (x) => x, {"In", "Out"})

From the matched pairs it expands the lists in 2 columns In for starttime, Out for out time. 

End result doesnt give the correct duration.

ATTEMPT 2:
Tried all 3 options with calculated columns and indexcolumns from this post: Calculating-time-between-check-in-and-check-out-from-different 

But this way also cannot handle multiple IN's or OUT's in a row. 

Please help me in the right direction!

[mahoneypat · Accepted solution]

The simplest way to do this would be to first create a calculated column (DAX is better than M for this one), with the column expression below (gives the time in hours at the status on the current row by finding the next status update (in or out)). Replace Times with your actual table name.

Time At Status =
VAR thisemployee = Times[EmployeeID]
VAR thistime = Times[Entry created at]
VAR nexttime =
CALCULATE (
MIN ( Times[Entry created at] ),
ALLEXCEPT ( Times, Times[EmployeeID] ),
Times[Entry created at] > thistime
)
RETURN
IF ( NOT ( ISBLANK ( nexttime ) ), ( nexttime - thistime ) * 24 )

You can then use a simple measure to just count the "in" or "out" time to get your result.

In total = CALCULATE(SUM(Times[Time At Status]), Times[Status] = "In")

Pat

[eheitmeier]

When I do this I get the following error code: A single value for column 'EmployeeID' in table 'Times' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Do you know how to resolve? 

[TiasHooijenga]

Pat, 

Thank you for this! On a small sample set of the data this works perfectly!

It is as you know quite memory intensive at the moment... (I run out of my 128GB memory)
In PowerQuery I would know how to do these calculations before merging my different input files/periods etc. 
Now that it's a calculated DAX column, I don't know any tricks to run this calculation on a smaller set of my data. 
How would I run this for every date/day, for every employee?