Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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.
@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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.