Forum Discussion
How to prevent automatic datatype conversion with database connection
I have Power BI Report Server (May 2023) pulling in data from an ODBC connection. One of the fields is a duration field in the format 00:00:00 (hours, mins, secs). Since the numbers look like a time format (20:21:34) Power BI is converting the field to a time field and displaying 8:21:34 pm. I can't for the life of me figure out how to prevent this from happening. When using a database/odbc connection Power BI doesn't appear to add a step that converts the data. It appears to be doing it internally with no way to remove the step or disable it. I've turned off the obvious settings below and it doesn't seem to make a difference.
"Type Detection" is set to "Never detect column types and headers for unstructured sources"
"Detect column types and headers for unstructured sources" has been disabled at the file level.
Time Intelligence has been disabled on the global level and the file level
Any ideas?
7 Replies
Power BI does not have a duration datatype, the only type that supports Hours : Minutes : Seconds is the datetime datatype Data types in Power BI Desktop - Power BI | Microsoft Learn . The only work around I can think of is to convert the duration to a whole number in terms of total seconds duration.
I forgot to mention that I'm simply trying to get it to import as text. The underlying data is a duration but I'm transforming the data to extract days. The data transformation used to work perfectly before it decided to start converting the data to time format.
ChrisMajestic wrote:
I forgot to mention that I'm simply trying to get it to import as text. The underlying data is a duration
Then you have to add a step doing an explicit cast or conversion to text. Otherwise Power BI will attempt to use the closest compatible data type.
