Convert text string of number time within Power Query into numerical days

[lazer1]

Hi,

I need to convert at time format like " 300 days, 3 hours, 05 minutes, 23 seconds"  into "300.128738425926" days.

I was able to figure out how to do it in excel with a custom function, but I haven't been able to figure out how to do this simply in power query. I don't want to do it with DAX because I need to append two, one is in the correct format, and one is in the text time format. Changing it from the source is not an option. Does anyone have an idea on how to do this?

[Daryl-Lynch-Bzy · Accepted solution]

Hi @lazer1 - Great Question!!  I came up with a couple of ways to achieve this.  First the long way, then the better way.

This long winded approach....  We need to separate the Number from the Text, Split this in List, Convert the Hours/Minutes/Seconds into Total Seconds and then divide by ( 24 * 60 * 60 ) and finally add Days.  I looks something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwVUhJrCzWUTBWyMgvLQIyTBVyM/NKS1KBTCNjheLU5Py8lGKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Workflow Duration Avg" = _t]),
    #"Get Number and Columns" = Table.AddColumn(Source, "Number Text", each Text.Select( [Workflow Duration Avg] , { "0".."9" , "," }), type text),
    #"Text to List" = Table.AddColumn(#"Get Number and Columns", "Text Split", each Text.Split( [Number Text] , ","), type list),
    #"Transform List to Numbers" = Table.TransformColumns( #"Text to List", {{"Text Split",
 each List.Transform(_ , each Number.From(_) ) }} ),
    #"Duration Seconds" = Table.AddColumn(#"Transform List to Numbers", "Add Seconds", each Duration.TotalSeconds( #duration( 0 , [Text Split]{1}, [Text Split]{2}, [Text Split]{3} )), type number),
    #"Add Result" = Table.AddColumn(#"Duration Seconds", "Calculation", each [Text Split]{0} + [Add Seconds] / ( 24 * 60 * 60 ), type number)
in
    #"Add Result"

I would suggest creating a custom function like the following:

(#"Duration Text" as text) as number =>
let
    #"Get Number and Commas" = Text.Select( #"Duration Text" , { "0".."9" , "," }),
    #"Text to List" = Text.Split( #"Get Number and Commas" , ","),
    #"Transform List to Numbers" = List.Transform( #"Text to List" , each Number.From(_) ),
    #"Duration Seconds" = Duration.TotalSeconds( #duration( 0 , #"Transform List to Numbers"{1}, #"Transform List to Numbers"{2}, #"Transform List to Numbers"{3} )),
    #"Add Result" = #"Transform List to Numbers"{0} + #"Duration Seconds" / ( 24 * 60 * 60 )
in
    #"Add Result"

 
We want to use the Duration.FromText - PowerQuery M | Microsoft Learn and conver this to Number.  To achieve this we convert the text into this format "305.03:05:23", convert this to duration, and then convert to number.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwVUhJrCzWUTBWyMgvLQIyTBVyM/NKS1KBTCNjheLU5Py8lGKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Workflow Duration Avg" = _t]),
    #"Replace Text" = Table.AddColumn(Source, "Add Text", each Text.Replace(
 Text.Replace(
  Text.Replace(
   Text.Replace( [Workflow Duration Avg] , 
    " days, ", "."),
   " hours, ", ":" ),
  " minutes, ", ":"),
 " seconds", "" ), type text),
    #"Convert to Duration" = Table.AddColumn(#"Replace Text", "Add Duration", each Duration.FromText( [Add Text] ), type duration),
    #"Covert to Number" = Table.TransformColumnTypes(#"Convert to Duration",{{"Add Duration", type number}})
in
    #"Covert to Number"

[AntrikshSharma]

@lazer1 3 hours, 05 minutes, 23 seconds should be 11123 

=3*60*60
+5*60
+23

[lazer1]

Yes. Plus the seconds for 300 days. So converting the text string of

300 days, 3 hours, 05 minutes, 23 seconds

into raw seconds is what I am trying to figure out how to do. I can do it with a split column, then all the math, but I have many source columns with this info... so trying to figure out if I could do the calculation you did (plus the day seconds) in an add column, or in power query m