cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamieham
Helper I
Helper I

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
Post Prodigy
Post Prodigy

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
Responsive Resident
Responsive Resident

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.