CODEWITHSUNDEEP
excelpowerbipowerquerym
Peter
Peter

Reputation: 12375

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

Upvotes: 2

Views: 590

Answers (3)

David Leal
David Leal

Reputation: 6769

You can use an Excel formula. If you have your text durations in the range A2:A7, then it would be enough using the following simple formula in B2 to convert it to a numeric value representing the days (It works under O365, I haven't tested it under older versions):

 1*A2:A7

To understand what it does is basically the following calculation on each cell:

=SUM(TEXTSPLIT(A2,":") * {3600,60,1})/86400

it converts every element to seconds, sums it, and divides it by a number of seconds in a day: 24*60*60=86400. To visualize the result in the proper format, you just need to use the following one d.hh:mm:ss.

excel output1

as you can see it is able to return, 1s, 1min, 1h, 1day, 7days converting it to the appropriate unit of time.

If you want exactly the same format as the input entered in text format for your numeric values in range B2:B7, use instead: [hh]:mm:ss:

excel output1

Note: I have entered the input data as text prefixing them with an apostrophe (') and with the following format: hh:mm:ss. For example: '168:00:00.

Upvotes: 1

horseyride
horseyride

Reputation: 21393

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"

Upvotes: 2

Peter
Peter

Reputation: 12375

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"

enter image description here

Upvotes: 1

Related Questions