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
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")
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?)
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.