Time difference between two raws

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...

Need Help

MarcelBeug

8 years ago

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),

MarcelBeug

Community Champion

8 years ago

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"