Forum Discussion

dilumd's avatar
dilumd
Impactful Individual
8 years ago
Solved

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's avatar
    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),

     

     

dilumd's avatar
dilumd
Impactful Individual
8 years ago

Hi,

 

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

 

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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's avatar
    dilumd
    Impactful Individual
    8 years ago

    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

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        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),