Timeslot adjustments

Power Query
1

Hi, I want to calculate time difference between starttime and endtime for each employee. I want to calculate total recover hours. Please avice.
Initially employee start and endtime will be entered and difference between start and endtime is calculated

  • Employee endtime can be corrected same day or next day.
  • EmployeeID 1126 & 1135 endtime is corrected. If endtime changed then calculate correctedvalue =starttime -endtime
    recoveredvalue = arrivaltime - correctedvalue

image
image1228Ă—355 12.8 KB

2

Hi @dratan99,

See if this is helpful. Just copy this M code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY9LDsMgDESvUrEmksdfyFUi7n+NQFClkmbRWiyYJ3gaH0cC2FNO2MAbE9OL6k5lIaBdrRN9k36nTB6sJtHDOC0Pl/D95/V6IfbgYhUyM19ldi+GlQC70ZcMFC6llLrIftnyEtiHbJbTWoqy1ZkQ4ujzR0l69gZXuHvMJBo8JrV2Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmpID = _t, StTime = _t, EnTime = _t, InsDate = _t, ArTime = _t, CorValue = _t, RecValue = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"EmpID", Int64.Type}, {"StTime", type datetime}, {"EnTime", type datetime}, {"InsDate", type date}, {"ArTime", type number}, {"CorValue", type number}, {"RecValue", type number}}),
    RemoveColumns = Table.RemoveColumns(ChType,{"ArTime", "CorValue", "RecValue"}),
    GroupRows = Table.Group(RemoveColumns, {"EmpID", "StTime"}, {{"AllRows", each _, type table [EmpID=nullable number, StTime=nullable datetime, EnTime=nullable datetime, InsDate=nullable date, Date=date]}}),
    AddIndex = Table.AddColumn(GroupRows, "Custom", each Table.Sort(Table.AddIndexColumn([AllRows], "Index", 1, 1),{{"InsDate", Order.Ascending}})),
    ExpandCustom = Table.ExpandTableColumn(AddIndex, "Custom", {"EnTime", "InsDate", "Index"}, {"EnTime", "InsDate", "Index"}),
    Add_ArrTime = Table.AddColumn(ExpandCustom, "Arrival Time", each if [Index] =1 then Number.From( [EnTime] - [StTime]) else null),
    Add_CorTime = Table.AddColumn(Add_ArrTime, "Corrected Value", each if [Index]>1 then  Number.From( List.LastN([AllRows][EnTime], 1){0} -List.FirstN([AllRows][StTime], 1){0} ) else null),
    Add_RecTime = Table.AddColumn(Add_CorTime, "Recovered Value", each if [Index]>1 then Number.From( List.FirstN([AllRows][EnTime], 1){0} - List.LastN([AllRows][EnTime], 1){0} ) else null),
    CleanUpColumns = Table.RemoveColumns(Add_RecTime,{"AllRows", "Index"})
in
    CleanUpColumns

.
A simple measure like below should do it.

Total Recoverd =
SUM( TableName[Recovered Value] )

I hope this is helpful.

1 Like
3

Hi @dratan99, we’ve noticed that no response has been received from you since the 6th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

4

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!