Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
3 years ago
Solved

Convert time (hh:mm) to decimal

Hi

I have the following table

 

Time ReceivedTime Decimal
09:519.85
09:409.67
09:449.73
18:3718.62
16:0416.07
16:2816.47
01:301.50
08:328.53
09:349.57
12:1412.23
08:318.52
08:258.42
06:136.22
19:1119.18
10:4410.73
18:2118.35
11:1411.23

 

I wish to convert  the time field (hh:mm) into decimal.

 

thank you

 

Richard

 

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])

     

6 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    hi, cottrera 

    [a = Time.ToRecord(Time.FromText([Time Received])), b = a[Hour] + a[Minute] / 60][b]
    • cottrera's avatar
      cottrera
      Post 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
        Super 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"
  • cottrera's avatar
    cottrera
    Post Prodigy
    3 years ago

    Hi AlienSx

    I have added your code to my other code via the advanced editor.

     

    let
    Source = Exchange.Contents("xxxxxxxxxxxxxxxxxxx"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(Mail1, "DateTimeReceived", "DateTimeReceived - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"DateTimeReceived - Copy", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DateTimeReceived - Copy", "Time Received"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "DateTimeReceived", "DateTimeReceived - Copy"),
    #"Extracted Day Name" = Table.TransformColumns(#"Duplicated Column1", {{"DateTimeReceived - Copy", each Date.DayOfWeekName(_), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Day Name",{{"DateTimeReceived - Copy", "Day"}}),
    #"Inserted Hour" = Table.AddColumn(#"Renamed Columns1", "Hour", each Time.Hour([Time Received]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Hour", "Custom", each 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"


    However, when I tried to expand the table

     

     

    I receive this message

    Richard

     

     

    • AlienSx's avatar
      AlienSx
      Super User
      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
    Post Prodigy
    3 years ago

    Thats got it thank you for your patience , legend ğŸ˜€