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
jamieham
Helper II
Helper II

Importing Time durations (mm:ss) in Excel into Power BI

I'm trying to import some data from an excel spreadsheet that has a couple of columns that details time durations such as 31:27 (mm:ss) however when its imported it doesnt display correctly.

 

This is what it looks like in Excel

 

Ball In Play       Possession Time

31:2717:10
30:3914:12
30:3916:27
31:2714:17
 
This is what it looks like once imported into Power Bi
 
Ball In Play Time
0.00494212962962963
0.00351851851851852
0.00533564814814815
0.0039120370370370

 
Can you please help so I can dispaly the time durations as mm:ss in Power Bi.
 
Thanks
6 REPLIES 6
rsbin
Super User
Super User

Hello @jamieham 

 

I have been playing with second formats for most of today.  My suggestion is convert to Seconds in Excel and import.

Then in desktop use the following code:

 

Measure = 
VAR hours =
    ROUNDDOWN ( [BallinPlay] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [BallinPlay], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [BallinPlay], 60 ) )
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

 

Can't recall where I found this, but works for me. You will have to modify if you want to remove the hours.
Hope this works for you.
Regards,
DoubleJ
Solution Supplier
Solution Supplier

hi @jamieham 

Does the value 0.00494212962962963 in Power BI correspond to 31:27 in Excel? The decimal number represents days, so 0.00494212962962963 would be 7 minutes and 7 seconds. That would be a mismatch. If that's the case you might have to check how the values are formatted in Excel (are they [mm]:ss? or as text?)
Regards
JJ


The columns have been formatted under Custom format in Excel using the option (mm:ss)

amitchandak
Super User
Super User

@jamieham , I am not sure Why I am seeing that as one column in Power BI.

 

Change the Data type to time and check

Mariusz
Community Champion
Community Champion

Hi @jamieham 

 

All you need to do is add an extra step to convert this number to time, please see the attached for ref.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Thanks but this doesn't work, it says " the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

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.