CODEWITHSUNDEEP
powerbipowerquery
Henk
Henk

Reputation: 53

Power BI convert text mm:ss to time

Convert a text from minutes:seconds to datatype time

I have a column with time (running time) in format (h:)mm:ss how can i convert this to a datatype "time" because given functions always expect the hours which i most of the time not have.

i've tried duration.FromText but the input format doesnot apply. Nor the automatic Time parse... it fails on the mm:ss part. = Table.AddColumn(#"Added Custom", "Parse", each Time.From(DateTimeZone.From([Tijd])), type time) gives a lot of errors

Upvotes: 0

Views: 1384

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

You didn't showed how your data looks like, so I will assume each value can be one of these:

  • h:mm:ss - hours, minutes and seconds, where mm and ss are between 0 and 59, with a leading zeros for the single digit values (e.g. 1:01:05);
  • m:ss - minutes and seconds, where ss is between 0 and 59, with a leading zeros for the single digit values (e.g. 1:05);
  • s - only seconds (e.g. 5).

In this case, one thing you can do, is to re-format the data, before changing its type. Add a custom column like this:

if List.Count(Text.PositionOf([Time], ":", Occurrence.All)) = 0 then
    "0:00:" & Text.End("0" & [Time], 2)
else if List.Count(Text.PositionOf([Time], ":", Occurrence.All)) = 1 then
    "0:" & Text.End("0" & [Time], 5)
else
    [Time]

which will count how many times : occurs in the input string (using Text.PositionOf and List.Count), and add suitable prefix (taking care for the leading zeroes with Text.End):

enter image description here

Now you can change the data type of the newly added column to Duration or Time.

enter image description here

Upvotes: 2

Related Questions