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

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"