Calculate end time for a given employer for a specific activity

[Anonymous]

Hi, everyone.

I have the following task to complete in Power Query.

I have a table of employees with beginning time for a specific task they do in our warehouse. So an employee fills out beginning time in a table when he starts an activity we have in the warehouse (picking, packing, inbound receiving, etc.) but where they usually don't have to fill out the end time. 

So basically I have the following table:

AbkürzungKostenstelleDatumZeit (von)Zeit (bis)KommentarBerechnete EndzeitGeleistete Arbeitsstunden

grd	Wareneingang	21.09.2021	11:38:00
rep	Retoure	21.09.2021	12:26:00
rep	Retoure	21.09.2021		14:01:00
ses	WA Spedition	21.09.2021	10:00:00	12:00:00
ses	Wareneingang	20.09.2021	12:00:00	15:06:00
ses	Retoure	20.09.2021	15:10:00	15:40:00
ses	Wareneingang	21.09.2021	14:17:00
ses	Clearing	21.09.2021	14:27:00
ses	Feierabend	21.09.2021	22:02:00
sey	Kommissionierung	21.09.2021	16:26:00
sey	WA Spedition	21.09.2021	16:30:00

Here: Abkürzung - Employees's abbreviation, Kostenstelle - activity he/she is busy at, Datum - date, Zeit (von) - beginning time, Zeit (bis) - end time, Berechnete Endzeit - calculated end time (the one I am trying to figure out), Geleistete Arbeitsstunden - time spent on the activity. 

So I would like to take the beginning time of the next task an employee takes on/starts with on the same day and based an that calculate the length of time an employee spends on a certain activity. I would then aggregate that data for all employees for further analysis. 

I am able to implement that in Excel but I would like to implement it in Power Query. I am stuck after I group data by employee and date where I would the pick up the beginning time for the given employee on a given date. 

I would really love to get some help and any tips would be highly appreciated.

Best Regards,

[Jakinta]

I assumed that you omitted by mistake the start time in 3rd row, if so, this might help.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE9C8IwEAbgv1IyF7lcP9TbRHBx08GhdKj0KAGblsQO/ntTKtq0Rd3ewPuQyyXLRGVKEYpLYViz0lWhK3dEuYLtCgGlO0hJ0YYAXAxEHmbCcOvyie9NZ3jWRsL0/3ZEMQxtGRPIPvbIsu2n2gXnlkt1V42eyZ69JL7iSE7eA/6Eb5kQpL4cDeqhhOQHxT+u8waNSa7HCxnI/saFUYt1XKgfWLEprqzLKUD3HPTBw+VjU9fKWrc5B7uFe9LJPw3s68pTiuBN8ic=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Abkürzung = _t, Kostenstelle = _t, Datum = _t, #"Zeit (von)" = _t, #"Zeit (bis)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Zeit (von)", type time}, {"Zeit (bis)", type time}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Datum", type date}}, "hr-HR"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Abkürzung", "Datum"}, {{"A", each let t=_ in Table.AddColumn( Table.AddIndexColumn(t, "i",1,1) , "Arbeitsstunden", 
each if [#"Zeit (von)"]<>null and [#"Zeit (bis)"] <> null then [#"Zeit (bis)"] - [#"Zeit (von)"] else try t[#"Zeit (von)"]{[i]} - List.Max( {[#"Zeit (von)"], [#"Zeit (bis)"]} ) otherwise null   ) , type table [ #"Zeit (von)"=nullable time, #"Zeit (bis)"=nullable time,  Arbeitsstunden=nullable duration] }}),
    #"Expanded A" = Table.ExpandTableColumn(#"Grouped Rows", "A", {"Zeit (von)", "Zeit (bis)", "Arbeitsstunden"})
in
    #"Expanded A"

If thaw was not the case then nested Fill Down combined with the code above might be your solution.

[Anonymous]

@Hi, @Jakinta . 

I haven't missed the beginning time. It's the fourth column (Zeit (von)). The third column in my table is date.

What I would like to get is fetch the beginning time for the next task after I group data by the employee (abbreviation) and date.

I could either take the next entry from data sorted before by employee, data, beginning data or take the next biggest beginning time for the same employee on the same day. What I am struggling with is specifically to incorporate one additional step inside the group step as I am not that advanced in Power Query.

Regards,