Forum Discussion

tomshaw83's avatar
tomshaw83
Icon for Helper I rankHelper I
5 years ago
Solved

mm:ss.00 - converting to a duration

Hi all,

 

I have a lot of data in the format mm:ss.00 - however at present when I import it to powerbi it doesn't like the look of it as a duration. To convert it I am splitting the data into 3 columns minute / second / milisecond. Creating an additional hour column with a 0 for each row, then merging all four columns back together to get hh:mm:ss.00 - this then can be converted to a duration.

 

As I have a lot of data in this format, i was wondering if anyone else had encountered this problem, and if there was a simpler more elegant solution.

 

Thanks


Tom

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    See if this route is preferable

     

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjbXMzY1MjZSitWJVjIxtTI00jM0MjYGc40NrEzN9MwtLC2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Prefisso aggiunto" = Table.TransformColumns(Origine, {{"Colonna1", each "00:" & _, type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Prefisso aggiunto", "Personalizzato", each Duration.From([Colonna1]))
in
    #"Aggiunta colonna personalizzata"

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    See if this route is preferable

     

     

    let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS1MjbXMzY1MjZSitWJVjIxtTI00jM0MjYGc40NrEzN9MwtLC2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t]),
    #"Prefisso aggiunto" = Table.TransformColumns(Origine, {{"Colonna1", each "00:" & _, type text}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Prefisso aggiunto", "Personalizzato", each Duration.From([Colonna1]))
in
    #"Aggiunta colonna personalizzata"