Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:27 | 17:10 |
30:39 | 14:12 |
30:39 | 16:27 |
31:27 | 14:17 |
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")
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)
@jamieham , I am not sure Why I am seeing that as one column in Power BI.
Change the Data type to time and check
Thanks but this doesn't work, it says " the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |