Convert time (hh:mm) to decimal

[cottrera]

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	9.57
12:14	12.23
08:31	8.52
08:25	8.42
06:13	6.22
19:11	19.18
10:44	10.73
18:21	18.35
11:14	11.23

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

thank you

Richard

[cottrera]

Thats got it thank you for your patience , legend 😀

[cottrera]

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 · Accepted solution]

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

[AlienSx]

hi, @cottrera 

[a = Time.ToRecord(Time.FromText([Time Received])), b = a[Hour] + a[Minute] / 60][b]

[cottrera]

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]

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"