Problem with duration

Thanks @smoupre, in that case I'll also put an M response

@janisCNH,

In M, if you have the number of minutes, you can create the duration value like this (parameters are days, hours, minutes, seconds):

#duration(0, 0, [Minutes], 0)

And then, when you have the duration value, you can get the text representation you wanted like this:

 #"Changed Type" = Table.TransformColumnTypes(Source,{ {"DurationInMinutes", Int64.Type} }),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0, 0, [DurationInMinutes], 0)),
 #"Added Custom1" = Table.AddColumn(#"Added Custom", "DurationWithWeeks", 
 each Number.ToText(Number.IntegerDivide(Duration.Days([Duration]),7)) & ":" 
 & Number.ToText(Number.Mod(Duration.Days([Duration]),7)) & ":" 
 & Number.ToText(Duration.Hours([Duration])) & ":" 
 & Number.ToText(Duration.Minutes([Duration])))
in
 #"Added Custom1"

Which looks like this:

However, if you're ok with the representation of "Duration" column, it's even simpler :

    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"DurationInMinutes", Int64.Type} }),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each #duration(0, 0, [DurationInMinutes], 0))
in
    #"Added Custom"

Power Query can certainly handle durations, and nicely, but when the problem is reporting presentation, then we can't leverage most of the niceness of M. If there's no need to combine durations, only present values that will be static in reports (think labels rather than measures), then we can cast the duration to a string and be done with it.

The requirements in this thread are loose, but if I hear that there's a field containing a duration in minutes, I'd expect some of the requirements will be to find total and average duration. This wouldn't be possible if we passed a Power Query duration into our model. Either we'd cast to string where the only aggregates available would be count and (lexicographical) max/min, or we'd leave it as a PQ duration, and get a float in the model, which can be aggregated easily, but not presented without something like Greg_Deckler's mod arithmetic with rounding.