powerbidaxpowerbi-desktopazure-sql-server

Power BI is converting date to datetime - DirectQuery (Advanced Option)


I have a PowerBI report connected to Azure SQL Direct Query Mode (Advanced Option - where we can write SQL Statements).

I got date column in SQL DB (date type), but PowerBI is converting this to datetime, even it’s in date type in SQL DB. Now I cannot transform this column in PowerBI as its Direct Query. However, if I connect directly to table (Direct Query mode only, but not advanced option) it takes date column as date.

Sample Date values:

created_date
2002-02-19
2002-02-19
2002-02-19
2002-02-19
2002-02-19

Converted Date values by PowerBI

created_date
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00
19-02-2002 00:00:00

Any inputs on this will be highly appreciated.

Thank you,


Solution

  • That's a known problem, even if you use CAST or CONVERT to force the datatype in the query PowerBI will format it as a DateTime.

    You can change it thought, from the report view:

    1. select the field (in the field bar)
    2. open the "Modeling" section in the top bar, here you will be able to change the data type and formatting of the field to Short Date