Power Query - CSV file import with time values over 24h

[Lawro]

Hi all

I'm importing a CSV file with a column called 'Core Time'. This has a few time values in it with are over 24hrs long. I've tried formatting this column to [h]:mm in the source data however that doesn't help.

When imported into PQ I get this error:

Expression.Error: We couldn't parse the input provided as a Time value.
Details:
24:39:00

Trying to change type to duration doesn't help. What do I need to so that these times are preserved?

Thanks in advance!

 

[p45cal]

Could you attach a csv?

 

[Lawro]

Could you attach a csv?

Here's a sample

 

[p45cal]

It's a surprise to me (and others on the net) that PQ's Table.TransformColumnTypes can't handle times/durations >24hrs directly. However, #duration can.
See attached. Core Time column has its own formatting done on the sheet. Remove that formatting and you'll get decimal days.
This was awkward because only times greater than 24hrs had a seconds component :00 tacked on at the end of the strings.
Finally got it down to a one-liner step called ReplacedValue:

 Table.ReplaceValue(PromotedHeaders,null,null,(x,y,z) as duration=>let ss= List.Transform(Text.Split(x, ":"),Number.FromText) & {0}, dd=#duration(0,ss{0},ss{1},ss{2}) in dd,{"Core Time"})

where PromotedHeaders is the previous step.
Cell D1 is a named range called FilePath used to point the query to where the csv file is; this cell is going to need updating before refreshing the query.

 

[Lawro]

Thanks ever so much for your help. I'm on a mobile device currently and will try to look at your solution in more detail soon.

 

[Lawro]

This code worked a treat. Then using the PQ step to convert to total hours produced the output I needed. Thanks again.