Convert time id into time in Power Query

Hi Experts

how would you convert the following in Power Query to time HH:MM:SS

Time ID

74928 = 07:49:28

164554 = 16:45:54

124713 = 12:47:13

ansd so on....

Need Help

Anonymous

4 years ago

Hi Anonymous,

You can take a look at the following M query formula to convert number and text values to time:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PZVBkiUhCETv0uteCIhWnaXj3/8aA/n4s8oXRKmYgPX392PXjuXP7xc+vx3bGaGYoGO+4vip2IBiscI6JO3Im7WiIqj2irjsBSh21rbea4CVsV6tbNVXvq/v/gro2I70VSGUdSe31rV2JMPzVgTVTunp3jsB5LV3KAdA9zF36+0HJv9lm/wbFFvr6uIDymxnKjPpnGB+OaGBlRm4DSj2xtIVBtgtlDCKZ7bwEeCEFYsTBIrtvKsTGejYk0uOoB25+eiiKPX1Y6H6ClhnnlrXikNVDBwSTLd4vnRLw3xXqfNdA3W59qgurZyZh68AalWWPqqVYOp39qZ+DYo9a/wApi5cdGAct/3ieINi5q+abUB39cwuKTp1qVSpSwMdv9ejjm/lpvlo/wHyfWQlqnp6ZK9DO3I81H6ovimT+5Kovom7ugtQneYW8nqAztiG/0DHovzs1NHvHNqdObQ7WbkGDP1WJHIqEvnf/cxxP4m9dmQ++s1sfzPb3y5YqXoMEAvbxAScakz6AH1cM658gfH61fQM8Erkq4wH6NtXbqPs5pRSSnXduRWgdbvmqNdJ58R6MTixge4JzB3g9pl0MjBzUfswFw2qS63oqqOaiu3WtqGK2KOpQ8l+3aX3BiD/FWrYgXkxc82L2aBKxdbNUTrBq7rqBMFMhD8MhONYlsOdKUoWr6vNpFPfG7wGAm5d76m2AsiLG6FMpR82A+R+bLUxSmfXGy4HAXqlnkSNIDBeHN5ygDwqJf5rgrll8NIC4xn7DfC2VCNqP4DX65G16OfzDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TimeID = _t, TimeText = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeID", Int64.Type}, {"TimeText", type text}}),
    #"Added Custom" =
        Table.AddColumn(
            #"Changed Type",
            "TimeFromNumber",
            each #time(Number.RoundDown([TimeID] / 10000), Number.RoundDown(Number.Mod([TimeID], 10000) / 100), Number.Mod(Number.Mod([TimeID], 10000),100))
        ),
    #"Added Custom1" =
        Table.AddColumn(
            #"Added Custom",
            "TimeFromText",
            each Time.FromText(Text.PadStart([TimeText], 6, "0"))
        )
in
    #"Added Custom1"

Regards,

Xiaoxin Sheng

9 Replies

vojtechsima

Super User

4 years ago

Hi, Anonymous
Custom made solution here in Power Query M (New Column), make sure your ID is a Text, or you would have to use Text.From function:

let
Hour = (let
hourShort = Text.Start([DateNumber], 1),
hourLong = Text.Start([DateNumber], 2),
check = if Text.Length([DateNumber])=5 then "0"&hourShort else hourLong
in check),
MinutesSeconds = Text.End([DateNumber], 4),
Minutes = Text.Start(MinutesSeconds, 2),
Second = Text.End(MinutesSeconds, 2)
in
Hour & ":" & Minutes & ":"& Second

ribisht17
Super User
4 years ago
Hi Route,

Please check this Solved: Convert a number to 24 Hour time - Microsoft Power BI Community

Regards,
Ritesh
AntonioM
Solution Sage
4 years ago
Hi, If your time id is text you can use Time.FromText( ).

You could either add a new custom column

= Table.AddColumn(#"Source", "Time", each Time.FromText([Time Id]))

Or replace values in the column
= Table.ReplaceValue(#"Source",each [Time Id],each Time.FromText([Time Id]),Replacer.ReplaceValue,{"Time Id"})
- Anonymous
  Not applicable
  4 years ago
  Hi resolver how would you change if my TimeID column was a whole number
  - vojtechsima
    Super User
    4 years ago
    = Table.ReplaceValue(#"Source",each [Time Id],each Time.FromText(Text.From([Time Id])),Replacer.ReplaceValue,{"Time Id"})
    
    Anonymous