Forum Discussion

_power_bi's avatar
_power_bi
Helper I
1 year ago
Solved

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.

Need Help
  • _AAndrade's avatar
    _AAndrade
    1 year ago

    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.


6 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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's avatar
      _power_bi
      Helper I
      1 year ago

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

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

    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's avatar
      _power_bi
      Helper I
      1 year ago

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

    • _power_bi's avatar
      _power_bi
      Helper I
      1 year ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.