Power Query reading date as datetime datatype while using Direct Query

[_power_bi]

I have a very large table that I have to use direct query. The source is SQL Server. One of the fields is a datetime. When I connect to this source, it reads the field as datetime.  This doesn't work in the model because my date dimension must join on a date, not datetime.  When I cast, convert, use dartpart (doesn't matter), the field is either 1. a datetime, or 2. a string.  Either way, for the date dimesion to work, it must be date.  I changed the datatype from datetime to date in power query and it says "the step results in a query that is not supported in direct query mode."

I have seen some post as a workaround to create a view with the field as a date, then use that view. That is not possible as I don't have access to do that.  Looking for a way to pull this field in as a date with the requirements as mentioned above.

[Anonymous]

Hi @_power_bi 

Right-Click the datetime column and select Change Type then click date in power query.

Currently, the built-in date hierarchy is not yet available when using DirectQuery mode. You could click to upvote this idea or add your own comments.

As a workaround, you could create a custom date hierarchy manually. Please create [Year], [Quarter], [Month], [Day] columns first, right-click the original Date column and choose 'New Hierarchy', then, drag [Year], [Quarter], [Month], [Day] columns to place them under [Date] column.

Why there is no date hierarchy - Microsoft Fabric Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_AAndrade · Accepted solution]

Hi @_power_bi,

I will try one of this possible solutions:

1 - When conecting to SQL server use a query like this:

SELECT
    YourTable.*,
    CAST(YourDateTimeColumn AS DATE) AS NewDateColumn
FROM YourTable

2 - Add a Calcutated column using DAX and use it do make the realationship.

[_power_bi]

After some digging, I found two solutions. The first is what is proposed here.  You can make a calculated column using DAX and form the relationship with the date dimension using it.  However, I am not sure on performance on that and have not tested it. 

The other method which I think will work better is to create a datetime column in the date dimension table.  The column will display midnight for each day. This can be related to the datetime field in the table.  *The dates in this column were all at midnight. This would not work if they were coming through with the datetime. 

[_power_bi]

I already tried case (column_name as date) . It still reads it as a datetime.  see original post.

[Kedar_Pande]

You can create a calculated column

Date_Only = 
DATE(YEAR('YourTable'[DatetimeField]), MONTH('YourTable'[DatetimeField]), DAY('YourTable'[DatetimeField]))

After creating this column, you can now use this column (Date_Only) to join with your Date dimension table.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[_power_bi]

"the step results in a query that is not supported in directy query mode"