Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Convert time (hh:mm) to decimal

Hi I have the following table   Time Received Time Decimal 09:51 9.85 09:40 9.67 09:44 9.73 18:37 18.62 16:04 16.07 16:28 16.47 01:30 1.50 08:32 8.53 09:34 ...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    nonono, you don't want to add column to column. In addition to that your [Time Received] is not text but of "time" type. That's smth new to me. Replace your "lady in red" with this:

    #"Added Custom" = Table.AddColumn(#"Inserted Hour", "Time Decimal", each [a = Time.ToRecord([Time Received]), b = Number.Round(a[Hour] + a[Minute] / 60, 2)][b])

     

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
3 years ago

Hi AlienSx thankyou for responding so quickly. Im not sure I fully understadn  how to apply this logic in Power Query. Could you please expand RIchard

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAgDAN34d1HnAQKXgWx/xpUBFB/J/uc9J6kMSONZ5HLJV+ESnuDCsUPaQ0PtL34PD1b254Sflsc0hxUCAuvEdFCfn91Z1hXxgQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time Received" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Time Decimal", each [a = Time.ToRecord(Time.FromText([Time Received])), b = Number.Round(a[Hour] + a[Minute] / 60, 2)][b])
in
    #"Added Custom"