In power query, how to turn a number into a duration in seconds?

Question

I am trying, in power BI, to create change the type of one of my columns. Said column contain Numbers and I am trying to turn that number into a duration in seconds. But whenever I use the default type change, it turn the duration into days.

= Table.TransformColumnTypes(#"Changed Type",{{"Duration", type duration}})

is the default, I've tried puttin duration(seconds) or duration.seconds, but it didn't work. I've looked around for a solution, but all I get are DAX solutions. I couldn't find much about power query in general.

Thanks for the help

I think you could add a new column from examples, and multiply your cell number by the corresponding value (ex: from hours to seconds: column * 3600). It'll do that for every row. Then just delete your unwanted old column. — Dreekun

Marc Pincince Marc Pincince · Accepted Answer · 2020-01-30T16:35:46

I believe this does what you want.

If you start with a column called Seconds:

You can add a column with = Duration.From([Seconds]/86400) to get:

Alternatively, you could use:

= Table.ReplaceValue(Source, each [Seconds],each Duration.From([Seconds]/86400),Replacer.ReplaceValue,{"Seconds"})

to change...

directly to...

Here's the M code for the two different options:

Adding a column:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Duration", each Duration.From([Seconds]/86400))
in
    #"Added Custom"

Directly changing:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source, each [Seconds],each Duration.From([Seconds]/86400),Replacer.ReplaceValue,{"Seconds"})
in
    #"Replaced Value"

In power query, how to turn a number into a duration in seconds?

1 Answers