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 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's avatar
MarcelBeug
Community Champion
8 years ago

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