Direct Query - Date Format

[supersharp]

I am using both Impot and DQ to insert the same table from Azure SQL DB. Both are showing date/time columns in PowerQuery with the correct data type, however when I go to the report the DQ does not show the date/time format. It also appears as a date/time format in the toolbar. Tried all sorts of data conversion methods - any ideas?

Date/Time Columns: TimeInserted, TimeRefreshed. Top table is Import.

PowerQuery look at DQ table - correct format:

Report view of Query Table - incorrect format:

Data type: correct format?

[v-luwang-msft]

Hi @supersharp ,

• Auto date/time is unavailable in DirectQuery. For example, special treatment of date columns (drill down by using year, quarter, month, or day) isn't supported in DirectQuery mode.

details information refer，the following article tells limitations of use Direct Query:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery 

Best Regards

Lucien

[amitchandak]

@supersharp , First of all, make sure it is datetime in power query and then in Column tools(Data tab). I am not seeing a calendar icon , so bit doubtful.

Also you can choose a format of you choice in column tools. Choose one with datetime 

[supersharp]

Hi @amitchandak - I tried all those as per the images.

- PowerQuery shows the columns as date and time

- Column Tools shows it as date/time too

- However, in the Report View - Fields Pane, I do not have get date hierarchy to work with - which is odd (see the field selector second table). The exact same table is showing the date/time hierarchy when using Import Mode.