Clean column with different time records in powerbi

I have a Power BI table with a column called "hours". It can have different time records with following different formats:

PT5H15M{YearMonthDayTime}, PT0S{YearMonthDayTime}, PT5H15M, PT10H, etc.

How can I clean them up so that the hours are represented as numbers, for example, PT5H15M{YearMonthDayTime} would be 5,25 and PT3H30M would be 3,5.

Can't find any easy way to filter the column since some rows have {YearMonthDayTime} ending and others doesn't. I don't want to transform every record manually.

Thanks already!

Solution

You can achieve this in power query

Steps followed in Power Query

Extract Text between delimiters PT and H to populate Hours column. Change type to decimal
Extract Text between delimiters H and M to populate Minutes column. Change type to decimal
Replace Errors with zero. Replace all null values with zero.
Devide Minutes column by 60 to convert into hours.
Add Hours column in step 1) to hours column in step 4)
Convert result column type to Text
In result column: Replace . with , using Transform -> Replace Values

M code:

let
    Source = Excel.Workbook(File.Contents("C:\Ashok\Power BI\Stack Overflow\Data_02_jun2_2023.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Hours", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type1", "Text Between Delimiters", each Text.BetweenDelimiters([Hours], "PT", "H"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Text Between Delimiters.1", each Text.BetweenDelimiters([Hours], "H", "M"), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters1",{{"Text Between Delimiters.1", type number}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type2", "Division", each [Text Between Delimiters.1] / 60, type number),
    #"Changed Type3" = Table.TransformColumnTypes(#"Inserted Division",{{"Text Between Delimiters", type number}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type3", {{"Text Between Delimiters", 0}}),
    #"xyz" = Table.TransformColumns(#"Replaced Errors", {{"Text Between Delimiters", each if _ is null then 0 else _}, 
                                                        {"Text Between Delimiters.1", each if _ is null then 0 else _}, {"Division", each if _ is null then 0 else _}}),
    #"Changed Type4" = Table.TransformColumnTypes(xyz,{{"Text Between Delimiters", type number}, {"Division", type number}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type4", "Addition", each [Text Between Delimiters] + [Division], type number),
    #"Changed Type5" = Table.TransformColumnTypes(#"Inserted Addition",{{"Addition", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type5",".",",",Replacer.ReplaceText,{"Addition"})
in
    #"Replaced Value"