none
How in M to convert a decimal column with seconds into Duration? RRS feed

  • Question

  • I know there are probable several different ways. I'd be interested in knowing what are the different patterns, and also which pattern is most efficient.
    Thursday, October 15, 2015 5:32 PM

Answers

  • Here's a simpler M example:

    = let seconds = 90.0 in Duration.From(seconds/86400)

    And here's how you could use this approach on a table:

    let
        Source = Table.FromRecords({[Seconds=90.0]}),
        #"Added Duration" = Table.AddColumn(Source, "Duration", each Duration.From([Seconds]/86400), type duration)
    in
        #"Added Duration"

    (Bill's approach above could be done through the UI, and is doing essentially the same thing, just with separate steps.)

    Ehren




    Tuesday, October 20, 2015 5:26 PM
    Owner
  • Ok...so here is an example. :-)

    Put this code below in advanced editor window.

    let
        Source = Table.FromRows({{90,"something"},{75,"something2"}},{"Duration","Other"}),
        Divide = Table.TransformColumns(Source, {{"Duration", each _ / 86400}}),
        ChangeType = Table.TransformColumnTypes(Divide,{{"Duration", type duration}})
    in
        ChangeType
    Regards

    Friday, October 16, 2015 10:27 AM

All replies

  • So, are you working with two time values and trying to subtract them from one another to get duration, can you provide some sample data?
    Thursday, October 15, 2015 6:35 PM
  • I have a decimal number of seconds as 90.0.

    I wish to convert that to Duration so that I can display as "1:30". There are no DateTime values involved.

    Thanks, Chris

    Thursday, October 15, 2015 6:48 PM
  • Dividing column by 86400 and then changing its type to duration should be fast.....i think :-)

    Regards

    Thursday, October 15, 2015 9:50 PM
  • Thanks, but what I'm really after is an example in M since the syntax is new to me.
    Thursday, October 15, 2015 11:57 PM
  • Ok...so here is an example. :-)

    Put this code below in advanced editor window.

    let
        Source = Table.FromRows({{90,"something"},{75,"something2"}},{"Duration","Other"}),
        Divide = Table.TransformColumns(Source, {{"Duration", each _ / 86400}}),
        ChangeType = Table.TransformColumnTypes(Divide,{{"Duration", type duration}})
    in
        ChangeType
    Regards

    Friday, October 16, 2015 10:27 AM
  • Excellent. Thank you. Googling "M" rarely gets me anything useful.
    Friday, October 16, 2015 1:59 PM
  • Here's a simpler M example:

    = let seconds = 90.0 in Duration.From(seconds/86400)

    And here's how you could use this approach on a table:

    let
        Source = Table.FromRecords({[Seconds=90.0]}),
        #"Added Duration" = Table.AddColumn(Source, "Duration", each Duration.From([Seconds]/86400), type duration)
    in
        #"Added Duration"

    (Bill's approach above could be done through the UI, and is doing essentially the same thing, just with separate steps.)

    Ehren




    Tuesday, October 20, 2015 5:26 PM
    Owner
  • That looks interesting. Could you give a complete example query?
    Tuesday, October 20, 2015 5:43 PM
  • I've updated the answer above.

    Ehren

    Tuesday, October 20, 2015 7:28 PM
    Owner