Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |