Convert from text to decimal to calculate duration based on days.

[makarama]

 

I am working with the power query editor in power bi (M).

I want to transform durations based of a column of texts in the format:

0 Seconds

1 Minute

2 hours 35 Minutes

3 Days 12 Hours 10 Minutes

57 Minutes

in terms of days. (decimals).

As an example assuming that this column had 2 rows:

1. "2 Hours 30 Minutes 0 Seconds" => 0.10
2. "2 Days 1 Hour 1 Minute 1 Second" => 2.04

   what I tried so far but it gives only zeros:

   let
   durationText = [Full Total Downtime],
   splitText = Text.Split(durationText, " "),
   totalDays =
   List.Sum(
   List.Transform(
   splitText,
   each
   let
   numericValue = try Number.FromText(Text.BeforeDelimiter(_, " ")) otherwise null, // Extract numeric value
   unit = Text.AfterDelimiter(_, " "), // Extract unit (e.g., "Day", "Hour", "Minute", "Second")
   multiplier =
   if unit = "Day" then 1
   else if unit = "Hour" then 1/24
   else if unit = "Minute" then 1/(24*60)
   else if unit = "Second" then 1/(24*60*60)
   else 0
   in
   if numericValue <> null then numericValue * multiplier else 0
   )
   )
   in
   totalDays

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzBDcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Text", each [Duration]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1, Int64.Type),
    #"Capitalized Each Word" = Table.TransformColumns(#"Added Index",{{"Duration", Text.Proper, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Capitalized Each Word", {{"Duration", Splitter.SplitTextByDelimiter("s", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Duration"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Duration", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Duration", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Duration.1", "Duration.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Duration.1", Int64.Type}, {"Duration.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Duration.2]="Day" then [Duration.1] else
if [Duration.2]="Hour" then [Duration.1]/24 else
if [Duration.2]="Minute" then [Duration.1]/1440 else
if [Duration.2]="Second" then [Duration.1]/86400 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Index", "Text"}, {{"Duration", each List.Sum([Duration]), type number}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzFK6hEcQQQ4VisD1KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),

    #"Replaced Units" = Table.AddColumn(Source, "Duration", each Expression.Evaluate(Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({"A".."Z"})(List.Accumulate({{"DA","*86400"},{"HOU","*3600"},{"MINUT", "*60"},{"SECON","*1"}},Text.Upper([Text]),(s,c) => Text.Replace(s,c{0},c{1}))), each _<>"")," + "))/86400)
in
    #"Replaced Units"

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzBDcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Text", each [Duration]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1, Int64.Type),
    #"Capitalized Each Word" = Table.TransformColumns(#"Added Index",{{"Duration", Text.Proper, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Capitalized Each Word", {{"Duration", Splitter.SplitTextByDelimiter("s", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Duration"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Duration", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Duration", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Duration.1", "Duration.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Duration.1", Int64.Type}, {"Duration.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Duration.2]="Day" then [Duration.1] else
if [Duration.2]="Hour" then [Duration.1]/24 else
if [Duration.2]="Minute" then [Duration.1]/1440 else
if [Duration.2]="Second" then [Duration.1]/86400 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Index", "Text"}, {{"Duration", each List.Sum([Duration]), type number}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.