Converting time

[Capps]

Hi,

I've been trying to figure this out but haven't found a solution yet.

What I want as an example is

0.00:04:53

What I'm getting when the table loads from power
0.003391204

The code section looks like

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"* FIRST RELEASE DATE [dd/mm/yyyy]:", type date}, {"* AVAILABLE DATE [dd/mm/yyyy]:#(lf) must be same or later than release date", type date}, {"DURATION VIDEO", type duration}}),

 

[sandy666]

maybe

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "en-GB"), ".", {0, RelativePosition.FromEnd}), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Column1", type duration}})
in
    #"Changed Type1"

Column1		Column1
0.003391204		0.00:04:53

 

[Capps]

Hi, thank you for your response. This could be my lack of understanding.

I believe what you've suggested works but it's essentially what is happening.

I can already see the final "0.00:04:53" but when I load the table to excel it automatically converts to "0.003391204". How do I keep it as "0.00:04:53"?

 

[sandy666]

you need define type of data in last step as duration
as you can see I set data type as duration twice, so try the same

or/and in worksheet use Custom format: d.hh:mm:ss
and set Properties