time difference from different column in power query

[shashank_0107]

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. 

[dufoq3 · Accepted solution]

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]

[watkinnc]

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

--Nate