Forum Discussion

shashank_0107's avatar
shashank_0107
New Member
2 years ago
Solved

time difference from different column in power query

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:...
  • dufoq3's avatar
    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]