topic Re: How to prevent automatic datatype conversion with database connection in Report Server

How to prevent automatic datatype conversion with database connection

ChrisMajestic — Tue, 03 Oct 2023 17:42:57 GMT

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?

Re: How to prevent automatic datatype conversion with database connection

d_gosbell — Wed, 04 Oct 2023 00:12:35 GMT

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.

Re: How to prevent automatic datatype conversion with database connection

ChrisMajestic — Wed, 04 Oct 2023 00:21:44 GMT

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.

Re: How to prevent automatic datatype conversion with database connection

d_gosbell — Wed, 04 Oct 2023 00:41:19 GMT

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

Re: How to prevent automatic datatype conversion with database connection

ChrisMajestic — Wed, 04 Oct 2023 12:35:02 GMT

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.

Re: How to prevent automatic datatype conversion with database connection

d_gosbell — Wed, 04 Oct 2023 23:29:28 GMT

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.

Re: How to prevent automatic datatype conversion with database connection

ChrisMajestic — Thu, 05 Oct 2023 12:49:37 GMT

Sounds interesting. How do I do that?

Re: How to prevent automatic datatype conversion with database connection

d_gosbell — Thu, 05 Oct 2023 22:10:22 GMT

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.