How to prevent automatic datatype conversion with database connection

[ChrisMajestic]

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?

[d_gosbell]

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.

[ChrisMajestic]

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. 

[d_gosbell]

---

@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.

[ChrisMajestic]

I tried that but converting it to text doesn't revert the numbers back what they were prior to the transformation. In other words the previous number was 20:21:34 which it converted to 8:21:34 pm. So when I try to convert it to text it keeps it as 8:21:34 pm in text form instead of 20:21:34. 

[d_gosbell]

So this could also be an issue with type conversion in the ODBC driver. You could try doing the conversion to a string explcitly in a view or SQL query so that it's done by the source database engine.

[ChrisMajestic]

Sounds interesting. How do I do that?

[d_gosbell]

It depends on what sort of database server you are pulling the data from. In SQL Server you could use the CONVERT() function with the optional 3rd parameter to specify the format. The DBA for your source database might be able to help with this.