Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KaraWilson
Frequent Visitor

Showing time as [mm]:ss in Power BI

Hi.

So I am importing data from a .xlsx file where I have a column of minutes and seconds [m]:ss into Power BI, it's pulling through as text but when changed to Time in transform data it knocks the times out for example 207:38 imports as 0.144184028 and when changed to time becomes 03:27:38 which technically is correct but I need to see 207:38 [m]:ss.

Is there a way this can be done? I have looked on google but not be able to find an answer on this.

Many thanks

1 ACCEPTED SOLUTION
Subhro95
Regular Visitor

Hi @KaraWilson ,
As per my knowledge, there is no way to get a time format like your expectation on the end result yet. You can only keep it in text format.
Create a new column next to the column with a time value like " 3:27:38 " and then enter the following formula to get the expected result:

Modified Time =
(
    Var H = HOUR('Table'[Time])*60
    Var M = MINUTE('Table'[Time])
    Var S = SECOND('Table'[Time])
    Var E = H+M
    Var F = E&":"&S
    Return F)
 
The table will be your table name and [Time] will be changed to your column name.

Hope this helps.



View solution in original post

1 REPLY 1
Subhro95
Regular Visitor

Hi @KaraWilson ,
As per my knowledge, there is no way to get a time format like your expectation on the end result yet. You can only keep it in text format.
Create a new column next to the column with a time value like " 3:27:38 " and then enter the following formula to get the expected result:

Modified Time =
(
    Var H = HOUR('Table'[Time])*60
    Var M = MINUTE('Table'[Time])
    Var S = SECOND('Table'[Time])
    Var E = H+M
    Var F = E&":"&S
    Return F)
 
The table will be your table name and [Time] will be changed to your column name.

Hope this helps.



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.