Forum Discussion

Stabben23's avatar
Stabben23
Helper I
6 years ago
Solved

numbers to time power query

Hi, I have this numbers as my time value and want to format to time value like hh:mm:ss in power query. 

example 113, 1256, 211318, 144036, 2

expected 00:01:13, 00:12:56, 21:13:18, 14:40:36, 00:00:02

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    Stabben23 

     


    First change the type of your Column to "Text"

    Then

    Try this custom Column
    Change ColName according to your actual Column Name

     

    =let 
sec=Text.End([ColName], 2),
min=Text.End(
Text.BeforeDelimiter([ColName],sec),2),
hour=Text.End(
Text.BeforeDelimiter([ColName],min&sec)
,
2)
in 
#time(
    (if hour="" then 0 else Number.From(hour)),
    (if min="" then 0 else Number.From(min)),
    (if sec="" then 0 else Number.From(sec)))

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    Stabben23 

     


    First change the type of your Column to "Text"

    Then

    Try this custom Column
    Change ColName according to your actual Column Name

     

    =let 
sec=Text.End([ColName], 2),
min=Text.End(
Text.BeforeDelimiter([ColName],sec),2),
hour=Text.End(
Text.BeforeDelimiter([ColName],min&sec)
,
2)
in 
#time(
    (if hour="" then 0 else Number.From(hour)),
    (if min="" then 0 else Number.From(min)),
    (if sec="" then 0 else Number.From(sec)))