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.


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"