Forum Discussion
converting text in duration
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
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.
10 Replies
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.
I will assume you know how to get to the Power Query User Interface in Excel
- Right click in the left hand column (Queries)
- Select "New Query => Other Sources => Blank Query
- In the right hand column (Properties), select the "Name" and rename it. I used "fnDurationTextConv"
- From the top menu, select "Advanced Editor" and replace the code with the function code I show above
- In your main query, select to Add a Custom Column, and enter the formula "fnDurationTextConv([your_column_name_containing_the_text_string]).
- Set the data type to "Duration"
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
[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
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
