power bi table not displaying duration properly

[AlexKX]

This feels like im missing something obvious.  Im starting with a 'minute' count. (1) (for this example ignore that its negative) and I want the 'HH:MM" etc displayed in a tabel in power BI.  the 'Total time' column (2) exists in the database im pulling from, thats basically why im going for but I needed it to be summable instead of text so i tried to replicate it in my "Total hours:Minutes" column.

I did this
= Table.AddColumn(dbo_clock_entries, "Total Hours:Minutes", each [total_minutes]/1440)
= Table.TransformColumnTypes(#"Added hours minutes column",{{"Total Hours:Minutes", type duration}})

Then i just tried to add the value to a table 

The value displayed in the query editor (3) looks like what im going for, but the value that is displayed in the table is very different.  How do i ge the tabel to display what the editor is showing, prefereably after removing that long decimal chain.  I don't see a good way to play with the formatting of it in the table.

[v-ssriganesh]

Hello @AlexKX,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @AlexKX,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop and understood the challenge: durations created using total_minutes/1440 display correctly in the Power Query Editor but show as decimal days in the table visual.

As per your requirement, I have created a solution that converts total_minutes into a hh:mm format that works properly inside table visuals.

What I Did:

• Loaded sample data (see attached .pbix) and Created a calculated DAX column:

Formatted Duration =

VAR TotalMinutes = ClockEntries[total_minutes]

VAR AbsMinutes = ABS(TotalMinutes)

VAR Hours = INT(AbsMinutes / 60)

VAR Minutes = MOD(AbsMinutes, 60)

VAR Sign = IF(TotalMinutes < 0, "-", "")

RETURN

Sign & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00")

• Displayed this in a table visual.
  
  

Output:

Please find the .pbix file attached for your reference.

Best regards,
Ganesh Singamshetty

[AlexKX]

Almost works. not summable so its not perfect
If i just use bare numbers the rows collapse together with a sum
Using summable columns
the custom formatting doesn't do that though so it makes many rows instead
custom format doesn't collapse together

likely because 'sum' isn't selectable for this custom column.  

'sum' is an option for the bare numbersbut not for these

So ya.  Thanks bunches, I learned something, but not perfect yet.

[v-ssriganesh]

Hi @AlexKX,
To assist you further, could you please provide sample data that clearly illustrates the issue you're experiencing?

• Include a small dataset that fully captures the issue (preferably in table format or as a downloadable file, not just a screenshot).
• Avoid including any sensitive or unrelated information.
• Also share the expected outcome based on the sample data you provide.

Need help preparing or uploading sample data? You can refer to this helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will help us reproduce your scenario accurately and provide a precise solution.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @AlexKX,
I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[Selva-Salimi]

Hi @AlexKX 

I think you just need to go to visualisation table setting and change the format based on what you need. Like this:

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[AlexKX]

Its not giving me 'long time' as an option.  is that a custom entry or something?

[rohit1991]

Hi  @AlexKX 

It looks like your Total Hours:Minutes column is a proper duration type in Power Query, but once you bring it into a table visual, Power BI displays it as a decimal (fraction of a day), which isn’t very helpful.

To fix that, you can add a calculated column to convert it to a proper hh:mm format. Replace 'YourTable' with your table name in below formula

FormattedDuration =
VAR TotalSeconds = 'YourTable'[Total Hours:Minutes] * 86400
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
RETURN FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00")

[AlexKX]

so I put that formula where?  or do you mean in the visualization?

[Greg_Deckler]

@AlexKX Not in Power Query. The formula given is for a DAX calculated column. However, I would recommend doing it differently because the formula given is going to return Text and that's generally not optimal since it won't display in most visuals correctly. Use this instead: Chelsie Eiden's Duration - Microsoft Fabric Community. This uses a Custom Format String so the original Number data type is preserved.

[AlexKX]

ALMOST works.  not summable. so not perfect but its closer at least