Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
_power_bi
Helper I
Helper I

Power Query reading date as datetime datatype while using Direct Query

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.

1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

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.






Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi @_power_bi 

 

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

vzhengdxumsft_0-1729048205061.png

 

 

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
Super User
Super User

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.






Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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. 

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

Kedar_Pande
Super User
Super User

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

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.