Time difference between two raws

[dilumd]

Hi All,

I have a table like below,

As shown in the above table, I want to calculate the time difference between two rows where the difference is below 12 hours.

My expected results for the above highlighted one is as follows,

EMP No	Date In	In Time	Date Out	Out time	Hour Difference
018616	10/29/2017	8.13	10/28/2017	23.18	8.55 Hours

[MarcelBeug]

Apart from getting data from different rows on one row, this requires some formatting steps as clearly documented in the query below.

Edit: you may want to format the resulting times back to decimal numbers, which I forgot.

Edit 2: that can be done by converting the times to text, using locale "id-ID" and then convert to decimal number. 

let
    Source = Table1,

    #"**** Times in time format" =
        "Times are converted from decimal number to time, by first converting the numbers to text,#(lf)" &
        "and next converting to time, using locale ""id-ID"" (Indonesia), which uses ""."" as minute/hour separator.",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In_Time", type text}, {"Out_Time", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"In_Time", type time}, {"Out_Time", type time}}, "id-ID"),

    #"**** Merge dates and times" =
        "Dates en times are merged to date/time format in new columns.",
    #"Inserted Merged Date and Time" = Table.AddColumn(#"Changed Type1", "In_DateTime", each [In_Date] & [In_Time], type datetime),
    #"Inserted Merged Date and Time1" = Table.AddColumn(#"Inserted Merged Date and Time", "Out_DateTime", each [Out_Date] & [Out_Time], type datetime),

    #"**** Get data on same row" =
        "In order to get data from the previous row on the current row:#(lf)" &
        "- 2 indices are added, staring with 0 and 1 respectively#(lf)" &
        "- the query is merged with itself on the different indices, naming the merged tables ""Previous""#(lf)" &
        "- columns are expanded, including the original column name as prefix,#(lf)" &
        "- the original sort order is restored.",
    #"Added Index" = Table.AddIndexColumn(#"Inserted Merged Date and Time1", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"EMP_No", "Out_Date", "Out_DateTime", "Out_Time"}, {"Previous.EMP_No", "Previous.Out_Date", "Previous.Out_DateTime", "Previous.Out_Time"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),

    #"**** Time difference" =
        "The time difference is calculated and rows are selected if difference < 12 hours.",

    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Hour Difference", each if [EMP_No] <> [Previous.EMP_No] then null else [In_DateTime] - [Previous.Out_DateTime]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Hour Difference] < #duration(0, 12, 0, 0)),

    #"**** Finishing touches" =
        "Formatting, selecting and renaming columns.",

    #"Extracted Hours" = Table.TransformColumns(#"Filtered Rows",{{"Hour Difference", each Text.From(Duration.Hours(_)) & "." & Text.From(Duration.Minutes(_)) & " hours", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Hours",{"EMP_No", "In_Date", "In_Time", "Previous.Out_Date", "Previous.Out_Time", "Hour Difference"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"In_Time", "In Time"}, {"Previous.Out_Date", "Date Out"}, {"Previous.Out_Time", "Out time"}, {"In_Date", "Date In"}})
in
    #"Renamed Columns"

[dilumd]

Hi @MarcelBeug

Below query worked for me since last November 2017. but now I’m getting the below error message which I don't understand the reason.

Kindly check the below error message and advice. Thank you in advance.

Error Message

[dilumd]

Hi @MarcelBeug

Thanks a lot for the reply! 

I tried your code and I'm getting below error,

Error

I have tried promoting the headers and defineing the column headers again but still i'm getting the same error. BTW I have few more columns in the data set. 

I'm in Sri Lanka so should i change the locale ID?

[MarcelBeug]

You get the error essage because you are refering to the Source step instead of the Navigation step.

Remark: probably the Navigation step has another step name if you check the advanced editor, this is the name you should use instead of "Source".

The locale ID is hard coded in the code, so you don't need to change your locale ID.

You may prefer locale "si-LK", which has also a "." as minute/hour separator.

If this is your Locale, then you can just change the type from text to time (and vice versa) without specifying the locale ID.

[dilumd]

Hi,

Yes you are correct! I made that change and now it gives below error.

[MarcelBeug]

Possibly, the previous step, in which times were converted to text, resulted in times with a decimal comma, rather than a decimal point. In that case, you can to add locale "si-LK" to the code:

#"Changed Type" = Table.TransformColumnTypes(Source,{{"In_Time", type text}, {"Out_Time", type text}}, "si-LK"),

[dilumd]

Hi @MarcelBeug

I did that also, but still i'm getting the same error, I don't what's wrong with this.

Step OneStep two

[Zubair_Muhammad]

@dilumd

Hows the Hour Difference computed?

[MarcelBeug · Accepted solution]

One issue is that some times have no decimals. This can be solved by using Number.ToText with format "n2".

I replaced step #"Changed Type":

    NumberToText = Table.TransformColumns(Source,{{"In_Time", each Number.ToText(_,"n2"), type text},{"Out_Time", each Number.ToText(_,"n2"), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(NumberToText,{{"In_Time", type time}, {"Out_Time", type time}}, "si-LK"),

This doesn't solve the problem that the texts are not converted to time, for which I have no explanation.

It works OK with me (see picture below).

Plan B would be a different approach and convert the numbers to time by taking the whole number for the hours and 100 * the fraction as minutes (this must be rounded).

I replaced both Changed Type steps by the step NumberToTime:

    NumberToTime = 
        Table.TransformColumns(
         Source,
         {{"In_Time", each #time(Number.IntegerDivide(_,1),Number.Round(100*Number.Mod(_,1),0),0), type time},
         {"Out_Time", each #time(Number.IntegerDivide(_,1),Number.Round(100*Number.Mod(_,1),0),0), type time}}),

    #"**** Merge dates and times" =
        "Dates en times are merged to date/time format in new columns.",
    #"Inserted Merged Date and Time" = Table.AddColumn(NumberToTime, "In_DateTime", each [In_Date] & [In_Time], type datetime),
 

[dilumd]

Thanks @MarcelBeug

Plan B does the job for me.