Forum Discussion

cmiller1221's avatar
cmiller1221
Regular Visitor
5 years ago
Solved

Custom column gives a data type error when switching from mixed to full import mode

Hello everyone,

 

I was ready to have my associates review my Power BI report and the last thing I needed to do was switch to import mode. After switching my duration function is now throwing a data type error. It works completley fine when on mixed mode but when it is in full import mode It gives me an error. I understand the error is that I am trying to use a string in a fixed decimal data type equation, but I do not understand why import mode would cause this.

 

I have spent way to long trying to figure out how to get this column to work and am basically out of ideas for it to do exctly what I want besides this script. I have reached out to my organizations internal Power BI team and even they are stumped.

 

Any feedback would be appreciated.

 

SCRIPT 

Duration (hh:mm) =

Var Duration = ABS(DATEDIFF('Sin Info with Sites - Prod'[DiscoveredDate], 'Sin Info with Sites - Prod'[ResolvedDate],SECOND))
VAR Hours =
INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)

// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ".", M )
)

 This is the working column in mixed modeThis is the error column in import mode

Need Help

4 Replies