Forum Discussion

shashank_0107's avatar
shashank_0107
New Member
2 years ago
Solved

time difference from different column in power query

time difference calculation issue

hello everone, 

I am working on some project, here i need to calculate time difference between 2 time columns. but i am facing problem to calculate time when date changes in midnight after 00:00. As you can see in screenshot attached, the time 12.45am is of 27.05.2014. so when i am subtracting two TIME in power query it assumed that 12:45am is of 26.05.2014. that's y showing negative time difference. there is a huge data in which this error is coming. 

 

anyone guide me further. thanks in advance. 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi shashank_0107, check this:

     

    Result

     

    version 1 (whole code)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTNzDVNzIwNFHSUTI0tDI0sDIwUAjwBfGMrExMQTxHX6VYHfxKDa1MLYEIxIuNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, From = _t, To = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"From", type time}, {"To", type time}}, "sk-SK"),
    Ad_DifferenceTime = Table.AddColumn(ChangedType, "Difference Time", each if [To] < [From] then (Date.AddDays([Date], 1)&[To]) - ([Date]&[From]) else ([Date]&[To]) - ([Date]&[From]), type duration),
    Ad_TotalHours = Table.AddColumn(Ad_DifferenceTime, "Total Hours", each Duration.TotalHours([Difference Time]), type number)
in
    Ad_TotalHours

     

     

    version 2 (add this as custom column)

     

    [ a = Duration.TotalHours([To] - [From]),
  b = if a < 0 then a + 24 else a
][b]

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Do your math on these values as type datetime, and you will not have this issue.

     

    --Nate

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi shashank_0107, check this:

     

    Result

     

    version 1 (whole code)

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTNzDVNzIwNFHSUTI0tDI0sDIwUAjwBfGMrExMQTxHX6VYHfxKDa1MLYEIxIuNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, From = _t, To = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"From", type time}, {"To", type time}}, "sk-SK"),
    Ad_DifferenceTime = Table.AddColumn(ChangedType, "Difference Time", each if [To] < [From] then (Date.AddDays([Date], 1)&[To]) - ([Date]&[From]) else ([Date]&[To]) - ([Date]&[From]), type duration),
    Ad_TotalHours = Table.AddColumn(Ad_DifferenceTime, "Total Hours", each Duration.TotalHours([Difference Time]), type number)
in
    Ad_TotalHours

     

     

    version 2 (add this as custom column)

     

    [ a = Duration.TotalHours([To] - [From]),
  b = if a < 0 then a + 24 else a
][b]