Change data type problem in Direct query connection

[Divya904]

Hi Experts.

 I have a field named "SoldDate" in my data source of type string. I am connecting to this data source using Direct query method. Now, in power BI desktop when i am trying to change the data type of this field from string to date, power BI is not allowing me to do so. The message i get is : change the connection type from Direct query to import query.

Please help me to understand why it is so.  Is this not possible to change the data type of a field, when using a direct query connection method. 

Thanks in advance.

Regards,

Divya

[v-piga-msft · Accepted solution]

Hi @Divya904,

It seems that you may have hit the limitations of Direct Query.

Limited data transformations

Similarly, there are limitations in the data transformations that can be applied within Query Editor. With imported data, a sophisticated set of transformations can easily be applied to clean and re-shape the data before using it to create visuals (such as parsing JSON documents, or pivoting data from a column to a row orientated form). Those transformations are more limited in DirectQuery. First, when connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external ‘model’ is taken from the source. For relational sources, like SQL Server, it is still possible to define a set of transformations per query, but those transformations are limited for performance reasons. Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they are limited to those transformations that can reasonably be translated into a single native query. If you use a transformation that is too complex, then you will receive an error that either it must be deleted, or the model switched to Import mode.

Actually, use of DirectQuery does have potentially negative implications, as detailed in this section. Some of those limitations are slightly different depending upon the exact source that is being used.

Before you use Direct Query, you need to have a look at the limitations of Direct Query.

Best Regards,

Cherry