Not possible to convert a cell in a duration

[FloBar]

Hello, 

I am currently blocked with this Data type conversion I should have the reading 39:38:58. I am always missing 24 hours

Right after the import, I have this. 

I try to change it to time but I the first 24 hours aren't there

Could you please help me

Thanks 

[v-nuoc-msft · Accepted solution]

Hi @FloBar

@Zang_Mi Thank you very much for your prompt reply.

For your question, I would recommend creating a column to make changes to the date.

Here's some dummy data

“Table”

create a column.

Duration = 
VAR _time = HOUR([Temps en operation (h:mm:ss)]) + 24 & ":" & MINUTE([Temps en operation (h:mm:ss)]) & ":" & SECOND([Temps en operation (h:mm:ss)])
RETURN _time

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FloBar]

hello,

I still have the issue, this is not working

Thanks for your help

[Zang_Mi]

Hello, if the duration can be over 24 hours, then you need to transform the text to the format d.hh:mm:ss before converting it to duration data type.

For example: from 39:38:58 to 1.15:38:58

M Query with the example and steps to apply:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMra0MrawMrVQitWJVjI0tTIwsjIxVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Duration Format", each let
hours_to_days = Number.FromText(Text.BeforeDelimiter([Duration], ":"))/24,

days = Number.RoundDown(hours_to_days),

hours = (hours_to_days-days)*24,

minutes_and_seconds = Text.AfterDelimiter([Duration], ":"),

duration_text = 
Number.ToText(days) & "." & 
Number.ToText(hours) & ":" &
minutes_and_seconds

in duration_text),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Duration Format", type duration}})
in
    #"Changed Type"

 If this answer helps you, please give a kudo and mark it as solution ‌‌🙂.