Direct Query - Snowflake

[Anonymous]

Hi,

I have a SNOWFLAKE data source with one of the tables has around 68853508 rows hence using direct query connection.

On the query level, I'm trying to convert one field from DateTime(UTC) to DateTime(NZT) and eventually converting DateTime(NZT) into a date.

I'm not able to do it due to the direct query connection type, any suggestions?

Thanks.

[v-jingzhang]

Hi @Anonymous 

For the performance consideration, some transformation steps are not supported in DirectQuery mode. It is suggested to do this tranformation step at the data source side for better performance.

Another option in Power Query is to write a custom SQL query statement when you add a data source. I take SQL Server for example, you could write a SQL statement to add offset hours to UTC datetime column and extract date part into a column. This can be done in the first Source step for a DirectQuery table. But you cannot do any further transformation step in Query Editor, otherwise it will remind you the "This step is not supported in DirectQuery mode" message again. In this way, you need to include all transformation operations in your SQL statement.

select 
	DatabaseLog.PostTime as UTC_Time, 
	DATEADD(HOUR,12,DatabaseLog.PostTime) as NZT_Time, 
	CONVERT(DATE,DATEADD(HOUR,12,DatabaseLog.PostTime)) as NZT_Date 
from 
	DatabaseLog

Hope this helps.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@Anonymous , few column operations are not supported in direct query mode. Try to have that column in snowflake table or view