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}}),

Answer 2

Here is another method, using a custom function named fnDecimalDuration

Custom Function Code
Paste into blank query and rename

(durationString as text)=>

let

//split on space
    split = Text.Split(durationString," "),

//convert values to numbers
    values = List.Transform(List.Alternate(split,1,1,1), each Number.From(_)),

//trim the terminal 's' from plurals
    unitNames = List.Transform(List.Alternate(split,1,1,0),each Text.TrimEnd(_,"s")),

//translation
    unitMultipliers = List.ReplaceMatchingItems(unitNames, {{"Day",1}, {"Hour",1/24},{"Minute",1/1440},{"Second",1/86400}}),


    combine = List.Zip({values, unitMultipliers})   

in
    List.Sum(List.Transform(combine, each List.Product(_)))

You can use this in your native query to either Transform or to Add a new column:

Transform Code

   #"Transform Decimal Durations" = Table.TransformColumns(#"Previous Step", {"Column1", each fnDecimalDuration(_), type number}),

Add Custom Column Code

 #"Added Custom" = Table.AddColumn(#"Previous Step", "Duration", each fnDecimalDuration([Column1]), type number)

You would add those steps into your code. An example for adding a custom column is shown below

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Commented out but could use this instead
//    #"Transform Decimal Durations" = Table.TransformColumns(#"Changed Type", {"Column1", each fnDecimalDuration(_), type number}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each fnDecimalDuration([Column1]), type number)
in
    #"Added Custom"