converting text in duration

[Anonymous]

Hi guys!

I do have some issues with converting text to time. 

our system gives me:

9m7s780ms

9m7s40ms

9m6s9ms

9m658ms

9m658ms

sometimes even with hours which would be:

1h5min23s253ms

i did not yet find a way to convert this in duration.

does anybody have an idea? i do have multiple colums and and abouut 70k rows 

i do not need the miliseconds but so far i have not found a good way to convert in the right form.

Thank you for your help 

[dufoq3]

Hi @Anonymous, another solution (2 versions [v1 is a bit faster]):

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wssw1Lza3MMgtVorVgfBMEByzYksE29QCC9swwzQ3M8/IuNjI1BgkFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    v1 = Table.AddColumn(Source, "Duration1", each 
     [ a = Splitter.SplitTextByCharacterTransition({"a".."z"}, {"0".."9"})(Text.Lower([Column1])),
       b = Text.Combine(List.Select(a, each not Text.Contains(_, "ms")), "+"),
       c = List.Accumulate({{"h", "/24"}, {"min", "/24/60"}, {"m", "/24/60"}, {"s", "/24/60/60"}}, b, (s,c)=> Text.Replace(s, c{0}, c{1})),
       d = Duration.From(Expression.Evaluate(c))
     ][d], type duration),
    v2 = Table.AddColumn(v1, "Duration2", each 
     [ a = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"0".."9"}, x), {"0".."9"})([Column1]),
       b = List.Transform(a, (z)=>
              Text.Combine(List.ReplaceMatchingItems(
                  Splitter.SplitTextByCharacterTransition((x)=> List.Contains({"0".."9"}, x), (y)=> not List.Contains({"0".."9"}, y))(z),
                  {{"h", "/24"}, {"min", "/24/60"}, {"m", "/24/60"}, {"s", "/24/60/60"}}))),
       c = Text.Combine(List.Select(b, each not Text.Contains(_, "ms")), "+"),
       d = Duration.From(Expression.Evaluate(c))
     ][d], type duration)
in
    v2

[slorin]

[DB Pressdata.t_ges] not [[DB Pressdata.t_ges]]

Duration.From(
   Expression.Evaluate(
      Text.Replace(
       Text.Replace(
         Text.Replace(
           Text.Replace(
            [DB Pressdata.t_ges],
            "h","/24+"),
          "min","/(24*60)+"),
        "ms","/(24*60*60*1000)"),
     "s","/(24*60*60)+")
   )
)

 Stéphane

[Anonymous]

Did i do this wrong? i just created a new custom column but got this error

[slorin]

Hi

another solution 

Duration.From(
  Expression.Evaluate(
    Text.Replace(
     Text.Replace(
       Text.Replace(
         Text.Replace(
             [YourColumn],
             "h","/24+"),
         "min","/(24*60)+"),
      "ms","/(24*60*60*1000)"),
    "s","/(24*60*60)+")
  )
)

Stéphane

[Anonymous]

It seems as I Made a mistake

[ronrsnfld · Accepted solution]

Here is a custom function that

•  Takes as input the strings in the formats you show
• Splits them apart
• Determines the type of unit
• Combines them into a duration

(dur as text)=>

let 

//split on letter to digit to create list of the units and the identifier
    split1 = Splitter.SplitTextByCharacterTransition((c)=>not List.Contains({"0".."9"},c),{"0".."9"})(dur),

//split each of the above to create a list of lists
    split2 = List.Transform(split1, (sp)=>Splitter.SplitTextByCharacterTransition({"0".."9"}, (c)=>not List.Contains({"0".."9"},c))(sp)),

//determine the type of unit, then convert the value to a Number
//"Days" can be added if necessary.  Assumed to be zero (0)
    hrs = try Number.From(List.Select(split2, each _{1} = "h"){0}{0}) otherwise 0,
    mins = try Number.From(List.Select(split2, each _{1} = "m" or _{1} = "min"){0}{0}) otherwise 0,
    sec = try Number.From(List.Select(split2, each _{1} = "s"){0}{0}) otherwise 0,
    msec = try Number.From(List.Select(split2, each _{1} = "ms"){0}{0}) otherwise 0

in 
    #duration(0,hrs,mins,sec + msec/1000)

Used in a Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Durations", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration Values", 
        each fnDurationTextConv([Durations]), type duration)
in
    #"Added Custom"

Results

Please note that if you are going to be using Power BI Desktop (and not Excel), that the duration data type in Power BI may not support milliseconds. If that is the case in your usage, you may need to convert the durations into a plain numeric value.

[Anonymous]

thank you for your help 

as it seems I am too dumb to implement this could you give me some help?
I use this for an Excel sheet.
The miliseconds can also be thrown out

[ronrsnfld]

I will assume you know how to get to the Power Query User Interface in Excel

1. Right click in the left hand column (Queries)
2. Select "New Query => Other Sources => Blank Query
3. In the right hand column (Properties), select the "Name" and rename it. I used "fnDurationTextConv"
4. From the top menu, select "Advanced Editor" and replace the code with the function code I show above
5. In your main query, select to Add a Custom Column, and enter the formula "fnDurationTextConv([your_column_name_containing_the_text_string]).
6. Set the data type to "Duration"

[Anonymous]

Thank you! with some playing aroud i got it to work!

[ronrsnfld]

Great!

Since it seems to have worked, could you mark it as the answer?