Calculate average duration from text in power bi

Question

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 as are shown in the picture to a decimal number in terms of days.

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

Input Data

What I have tried:

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

In order to make a unique format but it does not give me the correct outcome.

Answer 1

You can try this in powerquery

#"Convert" = Table.TransformColumns(#"PriorStepNameHere",{{"Column1", 
    each let a=Text.Replace(_,"s",""),
    b = Text.Replace(a,"Day","*1+"),
    c = Text.Replace(b,"Hour","*1/24+"),
    d = Text.Replace(c,"Minute","*1/24/60+"),
    e = Text.Replace(d,"Second","*1/24/60/60+")
in Expression.Evaluate(e &"0"), type number}}),