Custom Column to calculate total Early Morning and Late Night Hours for every shift

[MBisceglia]

Hi Everyone!

I have a dataset with shifts organized by day and ID, so one record per day for every ID. I need to calculate how many Early Morning and/or Late Night hours the employee worked for the period of time given by "PunchInTime" and "PunchOutTime". 

EarlyMorning/LateNight hours are from 9PM to 6AM.

In some cases, there will be Zero hours. This what I  would like to get:

Date                 ID               PunchInTime                PunchOutTime                   EarlyMorning/LateNight Total Hrs.      

8/13/2020      3580     8/14/2020 6:01:00 AM      8/14/2020 2:01:00 PM                                0.00

8/14/2020      3580     8/13/2020 9:49:00 PM      8/14/2020 6:00:00 AM                                8.18

12/20/2019    8596     12/20/2019 4:51:00 AM    12/20/19 8:23:00 PM                                  1.15
12/15/2019    8596     12/15/2019 10:40:00 AM  12/16/19 12:17:00 AM                                3.15

Can anyone help me? Thanks in advance!

Michelle

[Jimmy801]

Hello @MBisceglia 

try this approach. It creates a list a times for normal working, then creates a list of times from the column IN - OUT. Then List.Difference give you then a list of minutes and applying List.Count does the count of minutes basically / 60 you have the hours in decimal format. Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY47DsQwCESvgqgjBYjzMV3SR9re8t32LHuyncSWnI8EBTPwmJR46XXoTUy44xWNOZwzTS7qIrTukH9fQjXTqvnZOXepXTVKoVL0EOvim4IXUl8cFDXIKI3Y3dBNoODjLU2xYCxuwyUJdB0fjCqQigd5QHQ6IGquc3FKxktxzn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, In = _t, OUT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"ID", type text}, {"In", type text}, {"OUT", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Date", Text.Trim, type text}, {"ID", Text.Trim, type text}, {"In", Text.Trim, type text}, {"OUT", Text.Trim, type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Date", type date}, {"In", type datetime}, {"OUT", type datetime}}, "en-US"),
    CreateListWithMinutesOfNormal = List.Transform(List.Numbers(0,15*60+1), each #time(6,0,0)+#duration(0,0,_,0)),
    AddColumns = Table.AddColumn
    (
        #"Changed Type1",
        "HoursLateNightEarlyMorning",
        each List.Count(List.Difference(List.Transform(List.DateTimes(_[In], Duration.TotalMinutes(_[OUT]-_[In]), #duration(0,0,1,0)), each Time.From(_)), CreateListWithMinutesOfNormal))/60, type number
    )
in
    AddColumns

This is the outcome

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[MBisceglia]

Hi @Jimmy801 

Thanks so much for your help! I'm very new to M language. I usually end up using DAX to solve this type of situation. 

I  tried pasting your Code starting with the #"Changed Type" line (I have a different source) and made a few changes based on the steps that I already had in my query but for some reason, it keeps showing me this error: "Token identifier expected" and highlights the last "type" (next to "number"). This is my query. Can help me to add those final steps?

let
Source = Sql.Database("MTAGBVMSQLPROD3", "OPM_Datawarehouse"),
dbo_ADPDailyTimeCard = Source{[Schema="dbo",Item="ADPDailyTimeCard"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_ADPDailyTimeCard, each ([EmployeeStatus] = "Active") and ([PunchOutDate] <> null and [PunchOutDate] <> #date(1900, 1, 1)) and ([DeptName] <> "Pensioned Employees")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"EventDate", "DeptCode", "DeptName", "EmployeeID", "EmployeeFirstName", "EmployeeLastName"}, {{"PunchInDate(Min)", each List.Min([PunchInDate]), type nullable date}, {"PunchOutDate(Max)", each List.Max([PunchOutDate]), type nullable date}, {"PunchInTime(Min)", each List.Min([PunchInTime]), type nullable datetime}, {"PunchOutTime(Max)", each List.Max([PunchOutTime]), type nullable datetime}}),
#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([EventDate] <> #datetime(2020, 4, 27, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 28, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 29, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 30, 0, 0, 0))),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each true),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"EventDate", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"EmployeeID", Order.Ascending},{"EventDate", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index","Police K-9","Police",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Metro Rail Car Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Bush Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Northwest Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Eastern Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Kirk Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Facilities Maintenance","General Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Body Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Lr Service&Inspection North Av","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Bus Inventory","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Treasury Fare Collection Repai","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Main Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","Transit Information Services","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","Metro Plant Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","Revenue Control","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Labor Warranties&Special Proj","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","Communications Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","Metro Traction Power","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","Metro Signals","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","Metro Maintenance Of Way","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","Truck Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","Light Rail Catenary Power","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","Air Conditioning Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","Metro Cleaning","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","Light Rail Railcar Systems","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","Operations Planning&Scheduling","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","Light Rail Maintenance Of Way","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value27" = Table.ReplaceValue(#"Replaced Value26","Purchasing","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","Manager Of Bus Maintenance","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","Brake Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","Light Rail Traction Power","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","Light Rail Signals","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value32" = Table.ReplaceValue(#"Replaced Value31","Metro Railcar Systems","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value33" = Table.ReplaceValue(#"Replaced Value32","Light Rail Transportation","LightRail Transportation",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value34" = Table.ReplaceValue(#"Replaced Value33","Access Control","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value35" = Table.ReplaceValue(#"Replaced Value34","Cashier & Sales","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value36" = Table.ReplaceValue(#"Replaced Value35","Supervisor Ctrl & Data Acquisi","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value37" = Table.ReplaceValue(#"Replaced Value36","Supervisor Ctrl & Data Acquisi","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value38" = Table.ReplaceValue(#"Replaced Value37","Light Rail Heavy Repair","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value39" = Table.ReplaceValue(#"Replaced Value38","Payroll And Special Projects","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value40" = Table.ReplaceValue(#"Replaced Value39","Payroll And Special Projects","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value41" = Table.ReplaceValue(#"Replaced Value40","Accounting","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value42" = Table.ReplaceValue(#"Replaced Value41","Metro Inventory","Metro Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value43" = Table.ReplaceValue(#"Replaced Value42","Material Management Manager","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value44" = Table.ReplaceValue(#"Replaced Value43","Material Management Manager","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value45" = Table.ReplaceValue(#"Replaced Value44","Light Rail Inventory","LightRail Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value46" = Table.ReplaceValue(#"Replaced Value45","Maintenance Training","Training",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value47" = Table.ReplaceValue(#"Replaced Value46","Print Shop","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value48" = Table.ReplaceValue(#"Replaced Value47","Bus Training","Training",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value49" = Table.ReplaceValue(#"Replaced Value48","Media Relations, Public Affair","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value49", "workday duration test", each Duration.TotalMinutes([#"PunchOutTime(Max)"]-[#"PunchInTime(Min)"])/60),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"EventDate", "DeptCode", "DeptName", "EmployeeID", "EmployeeFirstName", "EmployeeLastName", "PunchInDate(Min)", "PunchOutDate(Max)", "PunchInTime(Min)", "PunchOutTime(Max)", "workday duration test", "Index"}),
#"Filtered Rows3" = Table.SelectRows(#"Reordered Columns", each [workday duration test] < 24),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows3",{{"PunchInTime(Min)", "PunchInTime"}, {"PunchOutTime(Max)", "PunchOutTime"}}),

#"Changed Type1" = Table.TransformColumnTypes("Renamed Columns",{{"EventDate", type text}, {"EmployeeID", type text}, {"PunchInTime", type text}, {"PunchOutTime", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"EventDate", Text.Trim, type text}, {"EmployeeID", Text.Trim, type text}, {"PunchInTime", Text.Trim, type text}, {"PunchOutTime", Text.Trim, type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Trimmed Text",{{"EventDate", type date}, {"PunchInTime", type datetime}, {"PunchOutTime", type datetime}}, "en-US"),
CreateListWithMinutesOfNormal = List.Transform(List.Numbers(0,15*60+1), each #time(6,0,0)+ #duration(0,0,_,0)),
AddColumns = Table.AddColumn
(
#"Changed Type2",
"HoursLateNightEarlyMorning",
each List.Count(List.Difference(List.Transform(List.DateTimes([PunchInTime]),Duration.TotalMinutes(_[PunchOutTime] -_[PunchInTime]), #duration(0,0,1,0)), each Time.From(_)), CreateListWithMinutesOfNormal))/60, type number
)

in
AddColums

Thanks again

Michelle

[Jimmy801 · Accepted solution]

Hello @MBisceglia 

this is always the problem with advanced solutions. You cannot do it with the GUI and that means you have to dive into the code. So one point is to provide a working solution, and a completely other thing is to integrate that solution into your environment also because you provided the data in a bad quality and so I used PQ to shape it - means that my trimming steps are not needed in your code. However I tried to adapt your code accordingly and I hope that it will work. I also cannot test it. 

Another thing I saw is that all your replacing-steps for sure could be enhanced as well and could probably perform better, but that is another topic. Here the adapted code

let
Source = Sql.Database("MTAGBVMSQLPROD3", "OPM_Datawarehouse"),
dbo_ADPDailyTimeCard = Source{[Schema="dbo",Item="ADPDailyTimeCard"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_ADPDailyTimeCard, each ([EmployeeStatus] = "Active") and ([PunchOutDate] <> null and [PunchOutDate] <> #date(1900, 1, 1)) and ([DeptName] <> "Pensioned Employees")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"EventDate", "DeptCode", "DeptName", "EmployeeID", "EmployeeFirstName", "EmployeeLastName"}, {{"PunchInDate(Min)", each List.Min([PunchInDate]), type nullable date}, {"PunchOutDate(Max)", each List.Max([PunchOutDate]), type nullable date}, {"PunchInTime(Min)", each List.Min([PunchInTime]), type nullable datetime}, {"PunchOutTime(Max)", each List.Max([PunchOutTime]), type nullable datetime}}),
#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each ([EventDate] <> #datetime(2020, 4, 27, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 28, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 29, 0, 0, 0) and [EventDate] <> #datetime(2020, 4, 30, 0, 0, 0))),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each true),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"EventDate", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"EmployeeID", Order.Ascending},{"EventDate", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index","Police K-9","Police",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Metro Rail Car Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Bush Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Northwest Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Eastern Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Kirk Maintenance","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Facilities Maintenance","General Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Body Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Lr Service&Inspection North Av","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Bus Inventory","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Treasury Fare Collection Repai","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Main Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","Transit Information Services","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","Metro Plant Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","Revenue Control","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Labor Warranties&Special Proj","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","Communications Maintenance","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","Metro Traction Power","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","Metro Signals","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","Metro Maintenance Of Way","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","Truck Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","Light Rail Catenary Power","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","Air Conditioning Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","Metro Cleaning","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","Light Rail Railcar Systems","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","Operations Planning&Scheduling","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","Light Rail Maintenance Of Way","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value27" = Table.ReplaceValue(#"Replaced Value26","Purchasing","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","Manager Of Bus Maintenance","Bus Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","Brake Shop","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","Light Rail Traction Power","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","Light Rail Signals","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value32" = Table.ReplaceValue(#"Replaced Value31","Metro Railcar Systems","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value33" = Table.ReplaceValue(#"Replaced Value32","Light Rail Transportation","LightRail Transportation",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value34" = Table.ReplaceValue(#"Replaced Value33","Access Control","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value35" = Table.ReplaceValue(#"Replaced Value34","Cashier & Sales","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value36" = Table.ReplaceValue(#"Replaced Value35","Supervisor Ctrl & Data Acquisi","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value37" = Table.ReplaceValue(#"Replaced Value36","Supervisor Ctrl & Data Acquisi","Metro Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value38" = Table.ReplaceValue(#"Replaced Value37","Light Rail Heavy Repair","LightRail Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value39" = Table.ReplaceValue(#"Replaced Value38","Payroll And Special Projects","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value40" = Table.ReplaceValue(#"Replaced Value39","Payroll And Special Projects","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value41" = Table.ReplaceValue(#"Replaced Value40","Accounting","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value42" = Table.ReplaceValue(#"Replaced Value41","Metro Inventory","Metro Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value43" = Table.ReplaceValue(#"Replaced Value42","Material Management Manager","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value44" = Table.ReplaceValue(#"Replaced Value43","Material Management Manager","Bus Maintenance",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value45" = Table.ReplaceValue(#"Replaced Value44","Light Rail Inventory","LightRail Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value46" = Table.ReplaceValue(#"Replaced Value45","Maintenance Training","Training",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value47" = Table.ReplaceValue(#"Replaced Value46","Print Shop","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value48" = Table.ReplaceValue(#"Replaced Value47","Bus Training","Training",Replacer.ReplaceText,{"DeptName"}),
#"Replaced Value49" = Table.ReplaceValue(#"Replaced Value48","Media Relations, Public Affair","Admin",Replacer.ReplaceText,{"DeptName"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value49", "workday duration test", each Duration.TotalMinutes([#"PunchOutTime(Max)"]-[#"PunchInTime(Min)"])/60),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"EventDate", "DeptCode", "DeptName", "EmployeeID", "EmployeeFirstName", "EmployeeLastName", "PunchInDate(Min)", "PunchOutDate(Max)", "PunchInTime(Min)", "PunchOutTime(Max)", "workday duration test", "Index"}),
#"Filtered Rows3" = Table.SelectRows(#"Reordered Columns", each [workday duration test] < 24),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows3",{{"PunchInTime(Min)", "PunchInTime"}, {"PunchOutTime(Max)", "PunchOutTime"}}),

    CreateListWithMinutesOfNormal = List.Buffer(List.Transform(List.Numbers(0,15*60+1), each #time(6,0,0)+#duration(0,0,_,0))),
    AddColumns = Table.AddColumn
    (
        #"Renamed Columns",
        "HoursLateNightEarlyMorning",
        each List.Count(List.Difference(List.Transform(List.DateTimes(_[PunchInTime], Duration.TotalMinutes(_[PunchOutTime]-_[PunchInTime]), #duration(0,0,1,0)), each Time.From(_)), CreateListWithMinutesOfNormal))/60, type number
    )
in
    AddColumns

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[MBisceglia]

Hi @Jimmy801, 

Thank you so much for your help, the code worked perfectly! Thanks for taking the time!

Best, 

Michelle