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...
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.


_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

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.