Display time over 24 hours in Power BI

[ChloeSpurge]

Hi,

I have this pivot table in excel which sums weekly hours, then I've formatted it as [hh]:mm due to the sum being over 24 hours.

However when I import this table into power BI it will not let me change the time to be over 24 hours, matching the table above. Any ideas how I could makes the times correct (matching table above)? Maybe convert the decimal to a time?

 

[RoryA]

It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:

=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0))

 

[ChloeSpurge]

It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:

=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0))

Thank you it worked!

 

[ChloeSpurge]

It looks like you used the formula from post 8 instead of the one from post 4. The one in post 8 includes the functions that the PQ editor adds when you create a custom column, which is why you have ended up with a Table. The formula for the calculated column is just:

=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0))

Hi, this works well, however when the time is say 5:05, 0.21 as a decimal, it comes out the formula as 5:50, is there any why to change this so it shows minutes under 10 as having a 0 before? eg 5:05, 6:09, 7:03 etc

 

[RoryA]

Yes, you can use:

=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0), "D2")

 

[ChloeSpurge]

Yes, you can use:

=Number.ToText(Number.RoundDown(Duration.TotalHours([HoursColumn]),0))&":"&Number.ToText(Number.RoundDown(Duration.Minutes([HoursColumn]),0), "D2")

Thank you!