Duration more than 24 hours - transform duration column to DD:HH:MM:SS format

[Josef]

Hi guys,

I have a CSV fil with a duration column in HH:MM:SS fotmat. When I try to change the column type to 'Duartion' one value gives an error, because the value is larger than 24 hours and the standard duration format is HH:MM:SS (and does not contains days). Does someone know how to 'enlarge' the duration column and add 'days', so the format will be DD:HH:MM:SS (al hours >24 have to be converted to a day)? 

Many thanks in advance. 

[MFelix]

Hi @Josef ,

This as to do with the parsing of the hours and time functions on the query editor

You need to add a column with the following code:

let
    DurationFromYourText = (text as text) as duration => let
        Split = Text.Split(text, ":"),
        AsNumbers = List.Transform(Split, Number.FromText),
        Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2})
    in
        Duration,
    Invoked = DurationFromYourText([Duration])
in
    Invoked

Should work as expected.

This formula comes from the post:

https://stackoverflow.com/questions/38907024/power-query-parsing-hhmmss-durations-that-are-greater-t...

[Josef]

@MFelix thanks alot for your reply.

Do you mean I have to add this code in the Advanced Power Query Editor?

I tried to insert it in the current steps, but I'm not able to get it right. 

At the moment it looks like this:

Could you please help me out to inset the code in the right way? It would be appreciated a lot.

[MFelix · Accepted solution]

Hi  @Josef ,

No need to go to the advance editor. Go to the last step of yur query add a custom column and copy paste the code that I send in the previous post:

Be aware that twhere there is [Duration] you should place the name of the column you want to convert I have place Duration based on the image you shared.

[Josef]

@MFelix Amazing! Many thank this did the job 🙂