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?

 

[severynm]

I'm a bit confused here about your number formats. Take week 24 for instance. Which is one correct: 145320 hrs 41 mins or 6055 hrs 41 mins? Or are they just using different data?

Anyways, greater than 24hr time formats are not something PBI can handle on its own. Assuming the units in your Sum of Weekly Hours column is actually number of hours, you could kind of make your own in a new column. This new column will technically be a text column, so you could not apply numerical operations against it, but that's what the original decimal column is for.

new column = TRUNC([sum of weekly hours]) & ":" & FORMAT(([sum of weekly hours]-TRUNC([sum of weekly hours]))*60,"00")

 

[ChloeSpurge]

I'm a bit confused here about your number formats. Take week 24 for instance. Which is one correct: 145320 hrs 41 mins or 6055 hrs 41 mins? Or are they just using different data?

Anyways, greater than 24hr time formats are not something PBI can handle on its own. Assuming the units in your Sum of Weekly Hours column is actually number of hours, you could kind of make your own in a new column. This new column will technically be a text column, so you could not apply numerical operations against it, but that's what the original decimal column is for.

new column = TRUNC([sum of weekly hours]) & ":" & FORMAT(([sum of weekly hours]-TRUNC([sum of weekly hours]))*60,"00")

View attachment 58479

Hi, sorry I should have specified, below is the the format power BI turns the data into when it is imported, this is the decimal version of 145320:41, 144277:56 etc. durations. Maybe there's a way to turn the decimal number into a time format? matching 145320:41, 144277:56 etc.

Many thanks

 

[RoryA]

Load the column as a Duration type, then you can use something like:

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

where HoursColumn is the name of your duration column.

 

[ChloeSpurge]

Load the column as a Duration type, then you can use something like:

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

where HoursColumn is the name of your duration column.

Hi, thanks for the reply, I've tried this as a measure and new column but both have this issue pop up, any ideas?

 

[RoryA]

What version of PowerBI are you using? You'd do it in the Power Query editor, not as a measure.

 

[ChloeSpurge]

What version of PowerBI are you using? You'd do it in the Power Query editor, not as a measure.

I've got this version, I don't think its the pro version. How would I use this formula/code in the power query editor?

Many thanks

 

[RoryA]

Mine is 2.99.621.0 (Nov 21)

Add a new custom column and paste that formula in.

 

[ChloeSpurge]

Mine is 2.99.621.0 (Nov 21)

Add a new custom column and paste that formula in.

View attachment 58553

Great thanks I'll give this a go!

 

[ChloeSpurge]

Mine is 2.99.621.0 (Nov 21)

Add a new custom column and paste that formula in.

View attachment 58553

Hiya, I tried this but its put the word 'Table' in the new column?