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.


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.