Help Time Issues

[Anonymous]

Hello,

I am facing a problem with regards on the dashboard/report I am creating, the report is basically a Attendance Monitoring report, I would like to get the total hours of each employees, their absences, their lates, etc.

I am having a problem on the TIMEDIFF function of MySQL, please see attached screenshot.

Also, can you suggest on how can I improve my report please.

Thank you for all the help and effort.

Regards,

Arvin

[MFelix]

HI @Anonymous,

Why don't you do you MySQL iwthout the TimeDiff formula and do that in the power query editor, using the time formulas?

Regards,

MFelix

[Anonymous]

Hello @MFelix Thank you for your response, can you help me on how to do that? Sorry I am a newbie here.

Thank you.

[MFelix]

Hi @Anonymous,

Trying to replicate you data to give you some more information I notice that the main question you have is the in time is higher the the out time this is because you don't have a date/time in your format but only a time format, so if you look at the first record he enter at 9PM and leaves at 6AM this gives you a negative time. and the calculations are correct since from 6MA until 9PM is 14 hours.

You need to make the calculations of the dates, I assumed in my query that the duration was lower tha 24hours so only added 1 day to the hour. I have made a small query that you can copy to a blank query (used advance query editor) and made all the calculations you need.

Please check it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDSNzIwNFfSUTIytDIwACIg08DMysACxIzVASoyQlIEFDYxRigyhisyRlUEMwluaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Time_In = _t, Time_Out = _t]),
    Format = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time_In", type time}, {"Time_Out", type time}}),
    Duration_Calc = Table.AddColumn(Format, "Custom", each [Time_Out]-[Time_In]),
    Adjusted_Start = Table.AddColumn(Duration_Calc, "Time_In_Adjusted", each [Date]&[Time_In]),
    Adjusted_End = Table.AddColumn(Adjusted_Start, "Time_Out_Adjusted", each if [Time_Out]<[Time_In] then Date.AddDays([Date],1)&[Time_Out] else [Date]&[Time_Out]),
    Format_Adjusted = Table.TransformColumnTypes(Adjusted_End,{{"Time_In_Adjusted", type datetime}, {"Time_Out_Adjusted", type datetime}}),
    Duration_Adjusted = Table.AddColumn(Format_Adjusted, "Duration", each Duration.From ([Time_Out_Adjusted]-[Time_In_Adjusted])),
    Format_Duration = Table.TransformColumnTypes(Duration_Adjusted,{{"Duration", type duration}})
in
    Format_Duration

Regards,

MFelix

[Anonymous]

Hello @MFelix

Kindly see the attached image, its the same issues I encountered when I used MySQL Function timediff.

let
Source = MySQL.Database("localhost", "attendance", [Query="SELECT * #(lf)FROM attendance_monitoring#(lf)", ReturnSingleDatabase=true]),
Format = Table.TransformColumnTypes(Source,{{"attendance_date", type date}, {"time_in", type time}, {"time_out", type time}}),
Duration_Calc = Table.AddColumn(Format, "Custom", each [time_out]-[time_in]),
Adjusted_Start = Table.AddColumn(Duration_Calc, "time_in_Adjusted", each [attendance_date]&[time_in]),
Adjusted_End = Table.AddColumn(Adjusted_Start, "time_out_Adjusted", each if [time_out]<[time_in] then Date.AddDays([attendance_date],1)&[time_out] else [attendance_date]&[time_out]),
Format_Adjusted = Table.TransformColumnTypes(Adjusted_End,{{"time_in_Adjusted", type datetime}, {"time_out_Adjusted", type datetime}}),
Duration_Adjusted = Table.AddColumn(Format_Adjusted, "Duration", each Duration.From ([time_out_Adjusted]-[time_in_Adjusted])),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"attendance_date", type date}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","total_hours",Splitter.SplitTextByRepeatedLengths(5),{"total_hours.1", "total_hours.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position",":",".",Replacer.ReplaceText,{"total_hours.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"total_hours.1", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"total_hours.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"total_hours.1", "total_hours"}})
in
#"Renamed Columns"

Thank you.

[MFelix · Accepted solution]

Hi @Anonymous,

The information is different from what you add in you first post, the first record was 9:00 PM = 21:00 (24 Hours) in this you have 9:00 AM = 9:00 (24 Hours) so in fact this is different hours please check how you get the format of the time in / time out, I believe that is why you are having issues in calculating the corrects times.

Mfelix

[Anonymous]

Hey @MFelix Thank you !!! Its now working fine! Thank you for all the help!