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

[cmiller1221]

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

[amitchandak · Accepted solution]

@cmiller1221 , I doubt your datatype of the fixed decimal number.

For sum of duration refer to these

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[cmiller1221]

@amitchandak None of the links fit except the last one which worked perfectly!! Thank you so much!

[ToddChitt]

Can you verify that the data types as seen in Power Query or Import Mode are EXACTLY the same as they were when it was in Direct Query mode? Don't just assume by looking at the data in the Desktop, you need to note the data type(s) in Power Query.

Does the issue go away if you revert back to Direct Query?

[cmiller1221]

Thanks for the quick reply. 

I just triple checked again and everything is exactly the same. The custom column is identical and so is everything in Power Query. 

I do not believe you can revert back to direct query, but I have one file on direct and one file on import that are identical except for that. The direct query works and the import does not.