Problem with duration

[janisCNH]

Hello Community!

I wanted to create a report with Power BI but I had a problem. In one column the duration of a feedback is presented in minutes. In some cases the answer needs several days so it would be great if I could transform the duration into "weeks:days:hours:minutes".

It would be really helpfull if somebody may help me because I am new in Power BI

Regards Janis 

[Greg_Deckler · Accepted solution]

Brute force:

Measures:

MinutesPerHour = 60

MinutesPerDay = 1440

MinutesPerWeek = 10080

Custom Columns:

Weeks = ROUNDDOWN([Minutes]/[MinutesPerWeek],0)

Days = ROUNDDOWN(MOD([Minutes],[MinutesPerWeek])/[MinutesPerDay],0)

Hours = ROUNDDOWN(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay])/[MinutesPerHour],0)

MinutesLeft = ROUNDDOWN(MOD(MOD(MOD([Minutes],[MinutesPerWeek]),[MinutesPerDay]),[MinutesPerHour]),0)

Duration = IF(LEN([Weeks])=2,"",0) & [Weeks] & ":" & IF(LEN([Days])=2,"",0) & [Days] & ":" & IF(LEN([Hours])=2,"",0) & [Hours] & ":" & IF(LEN([MinutesLeft])=2,"",0) & ":" & [MinutesLeft]

[DominikPetri]

During import, divide the minutes by 1400 (e.g. during import). That will result in the fraction of a day (e.g. 0.41666 = 60 minutes) because a day has 1400 minutes (60 minutes times 24 hours).

Using M-code: 

Store this value in a new column "Days". Then add a new custom column using Duration.From([Days]). 5000 minutes will result in 3:11:20 (3 days, 11 hours and 20 minutes). If you need parts of this duration, use Duration.Days(), Duration.Hours() etc.

Best regards,

Dominik.

[arify]

Sorry, I'm a bit new in these forums, questions are always answered in DAX. Would it be pointless for me to help people with M code? Because M has nice ways of handling durations

[Greg_Deckler]

Not at all, @greggyb often answers in M. As an end-user focused tool, DAX tends to be the go to for many of the users of Power BI but M code solutions are just as viable.

[greggyb]

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.

[Greg_Deckler]

The problem I see with using M and duration from the original problem statement is the desired inclusion of weeks. That may not really be what was intended but things are constrained to how M handles durations. If the exact format of weeks:days:hours:minutes is desired, I don't see how you get there except a brute force approach (that could admittedly most likely also be handled by M)

Edit: Oh, nevermind, I do see the solution for the original format for weeks above. Oops!

[arify]

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"

[greggyb]

There's no duration data type in the Tabular engine. You'd have to manually do the mod arithmetic to determine the number of weeks, days, hours, and minutes an integer represents, and then concatenate those together. You'd end up with a string you can't do any sort of arithmetic on. If that's acceptable, you should be able to get by with INT() MOD() and the division operator.