CODEWITHSUNDEEP
powerquerypowerbi-desktop
Elgate
Elgate

Reputation: 113

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

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

Upvotes: 1

Views: 5428

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

I believe this does what you want.

If you start with a column called Seconds:

enter image description here

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

enter image description here

Alternatively, you could use:

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

to change...

enter image description here

directly to...

enter image description here

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"

Upvotes: 3

Related Questions