I have a column with time data in text format with more than 24 hours that I need to convert into a time format. However, times > 24 hours result in errors: "We couldn't parse the input provided as a Time value."

Sample Data:

CodePudding user response：

The Time data type is limited to 24 hours, but the Duration data type can be used instead. Here's the M-Code to achieve the transformation:

let
    Source = Table.FromList(
        {
            "00:00:00", 
            "00:00:01", 
            "00:01:00", 
            "01:00:00", 
            "24:00:00", 
            "168:00:00"
        }, 
        null, 
        {"Duration String"}
    ),
    #"Added Custom" = Table.AddColumn(
        Source, "Duration", each 
        let
            AllHours = Number.FromText(Text.BeforeDelimiter([Duration String], ":")),
            Days = Number.IntegerDivide(AllHours, 24),
            Hours = Number.Mod(AllHours, 24),
            Minutes = Number.FromText(Text.BetweenDelimiters([Duration String], ":", ":")),
            Seconds = Number.FromText(Text.AfterDelimiter([Duration String], ":", 1)),
            Duration = #duration(Days, Hours, Minutes, Seconds)
        in
            Duration,
        type duration
    )
in
    #"Added Custom"

CodePudding user response：

In powerquery, if data is in column Time, add column .. custom column ... with formula

= #duration(0,Number.From(Text.Split([Time],":"){0}), Number.From(Text.Split([Time],":"){1}),Number.From( Text.Split([Time],":"){2}))

then transform the type to duration

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom1" = Table.AddColumn(Source, "Custom", each #duration(0,Number.From(Text.Split([Time],":"){0}), Number.From(Text.Split([Time],":"){1}),Number.From( Text.Split([Time],":"){2})),type duration)
in  #"Added Custom1"