Search code examples
excelpowerbipowerquerym

More than 24 hours: We couldn't parse the input provided as a Time value

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:

Time
00:00:00
00:00:01
00:01:00
01:00:00
24:00:00
168:00:00
Solution

  • 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

    enter image description here

    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"