Best Power Query Table for Transformation - Creating Schedule Activities to Match Actual Activities

[Anonymous]

Good day, I recently created a PowerApp that captures the Activity Start and End of Employees based on their tagging. The result is this table.

EmployeeActivityDateActivity StartActivity End

A	Productive	12/15/2023	12:22:58 PM	12:48:31 PM
A	Productive	12/15/2023	12:48:31 PM	12:54:17 PM
A	Productive	12/15/2023	12:54:17 PM	12:54:20 PM
A	Productive	12/15/2023	12:54:20 PM	1:00:17 PM
A	Lunch	12/15/2023	1:00:17 PM	1:52:13 PM
A	Productive	12/15/2023	1:52:13 PM	1:52:14 PM
A	Productive	12/15/2023	1:52:14 PM	2:50:37 PM
A	Productive	12/15/2023	2:50:37 PM	3:18:59 PM
A	Productive	12/15/2023	4:03:58 PM	4:04:05 PM
A	Productive	12/15/2023	4:04:05 PM	4:04:13 PM
A	Productive	12/15/2023	4:04:13 PM	4:04:18 PM
A	Break	12/15/2023	4:04:18 PM	4:10:03 PM
A	Productive	12/15/2023	4:10:03 PM	4:10:04 PM
A	Productive	12/15/2023	4:10:04 PM	4:31:04 PM

I'm planning to create another app that creates a schedule for those agents, so I can generate a report fo their adherence% and to get the variance of minutes if planned/scheduled activity doesn't reflect to their actualy statuses.

In order to do so, what's the best table to create in Lists order to match the actual activity when exported to Power Query. 
 Table 1:
EmployeeShiftShift DateShift StartShift EndOpen Time StartOpen Time EndBreak 1 StartBreak 1 EndLunch StartLunch EndBreak 2 StartBreak 2 End

A

Productive

12/16/2023

9:00 AM

6:00 PM

9:00 AM

6:00 PM

11:00 AM

11:15 AM

1:00 PM

2:00 PM

3:00 PM

3:15 PM

Concern for Table 1: I am unsure on how I would add the Open Time activity between the intervals of non productive statuses. I'm thinking of creating additional Columns like Open Time 1, Open Time 2.

Table 2:
EmployeeActivityShift DateActivity StartActivity End

A	Open Time	12/16/2023	9:00 AM	11:00 PM
A	Break 1	12/16/2023	11:00 AM	11:15 PM
A	Open Time	12/16/2023	11:15 PM	1:00 PM
A	Lunch	12/16/2023	1:00 PM	2:00 PM
A	Open Time	12/16/2023	2:00 PM	3:00 PM
A	Break 2	12/16/2023	3:00 PM	3:15 PM
A	Open Time	12/16/2023	3:15 PM	6:00 PM

Concern for Table 2: 
- I won't be able to add the Shift Start and Shift End which I wanted to have in order to get the Login and Logout time of each employee.
- It would create multiple items which would fill up the list quickly.

Any ideas or suggestions are highly appreciated. Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous 

For the table2, you can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM1TCMnMTQWyDY30Dc30jQyMjIEcSysDAwVHX5CwIYgZ4KsUqwPR41SUmpitYIiuA6IOpsXQFFkLTmvgKoFMNGt8SvOSMzDUQxXpKBmhKcdphRFcizFWjxihazBG0kCkN4zhvjCD2RELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Activity = _t, #"Shift Date" = _t, #"Activity Start" = _t, #"Activity End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Activity", type text}, {"Shift Date", type date}, {"Activity Start", type time}, {"Activity End", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Shift Date]&[Activity Start]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Shift Date]&[Activity End]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type datetime}, {"Custom.1", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Index", "ShiftStart", each List.Min(Table.SelectRows(#"Added Index",(x)=>x[Employee]=[Employee] and x[Index]=List.Min(#"Added Index"[Index]))[Custom])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "ShiftEnd", each List.Min(Table.SelectRows(#"Added Index",(x)=>x[Employee]=[Employee] and x[Index]=List.Max(#"Added Index"[Index]))[Custom.1])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"ShiftStart", type datetime}, {"ShiftEnd", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Custom", "Custom.1", "Index"})
in
    #"Removed Columns"

Outpout

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.