Date Format Direct Query

[aarikc17]

I am using a direct query and I cannot use the formatting options for a date.

I am trying to create a report that shows when a unit is supposed to start (DD/MM/YYYY).

However, my data from my direct query is in a format with time added (DD/MM/YYYY HH:MM:SS).

So all of my data has 12:00:00AM on the end, this is taking up too much room on my report, and is not needed.

Everything I have tried so far I get "Not supported in Direct Query Mode".

Please help.

[rajBi11]

I found this helpful.

https://youtu.be/9DBL8znoils?si=5V5SsW1U8kriarey

[WannesLimbourg]

somebody who knows how to get a column for name day via directQuery

[RedMax]

Sadly neither of the below had worked for me, but what did work was to create a new column in Power Query using Add Column - > Date -> Day - > START OF DAY.

Afterwards, I was able to use this new column to connect to my Date Table.

[Anonymous]

You can create a column 
Date=LEFT('Table'Datetime,10)

the above column trims to 10 characters which is in DD/MM/YYYY format and you can change datatype of the column to date

[Anonymous]

Errors: 1. This step results in a query that is not supported in Direct Query mode. Switch all tables to Import mode

Solution: Don't change the data type while loading(tranforming )data. First load the data using Direct Query option without transforming and then do the following to change data type of your data.

Go to "Data Modelling tab"-> "Select or click on the Query or table you loaded using Direct Query-> Select the column that we need to change the Data type and format-> then go to an option in topmost corner under Modelling tab to change the column Data type and format.

2. This table uses DirectQuery and cannot be shown

Solution:

If we go to Data modelling tab in the Left corner below chart tab to change any column format, It will not show the table as we used Direct Query to load data from the database. It shows as" This table uses DirectQuery and cannot be shown". 

If we need to change the format of the column that is loaded using Direct Query, We need to go to "Data Modelling tab"-> "Select or click on the Query or table you loaded using Direct Query-> Select the column that we need to change the Data type and format-> then go to an option in topmost corner under Modelling tab to change the column Data type and format.

[CARAYOZ]

Hi, 

Do you know how to keep the format unchanged when export the data table to excel? 

Under column tools, Data type is "Date", Format is "Short Date". 

The Date in PowerBI desktop is shown like '2/01/2023',

BUT when I export, it become '2/01/2023 12:00:00 AM' in EXCEL

[v-shex-msft]

Hi @aarikc17,

Can you ensure you date format is suitable for your system setting?(for instance: uk date and us date) If not, you can convert it before change type to date.

I also test to change type without any issue.

Regards,

Xiaoxin Sheng

[aarikc17]

With so many others being able to change the data type I will try the above and report back at the end of the day.

[aarikc17]

@dtartaglia I tried selecting and unselecting the "Allow unrestricted measures in DirectQuery" and both did not let me format the data. I am also on the latest version of Power Bi.

@Hasan The formatting in on the modeling tab is greyed out and I cannot use that.

@v-shex-msft I am not sure what you mean by my system time setting. This is what my time looks like

I have to be missing something basic if you guys can change the data type in a DirectQuery......

[aarikc17 · Accepted solution]

I found this........ it worked........

https://community.powerbi.com/t5/Desktop/Unable-to-alter-number-formatting/td-p/38134

[djg1kor]

Hi,

Using direct query mode, power query changes may not apply but can work on model.

you can go to the modeling viewand go to the table, click on the column name which need to change the date format, right side in the properties pane you find general, formating, advanced options. In format option you can change the date type and date format.

Hope this will help.

[RandomWalker]

Thanks so much! This is the best no-code solution.

[dtartaglia]

If I'm in directQuery mode, open the Query Editor I'm able to change a column Data Type from Date/Time to Date.

[aarikc17]

After changing the data type

[Hasan]

you might want to sue the modeling option in home page. I jsut tried and it is working for me

[dtartaglia]

aarikc17 are you on the lastest version of BI Desktop?

[dtartaglia]

I initially had: "Allow unrestricted measures in DirectQuery" enabled. I disabled it and changing the format worked. I wonder if the setting didn't really change? If you click File->Options and settings->Options->DirectQuery and enable: "Allow unrestricted measures in DirectQuery", can you change the format? My test source is a SQL view.

[Anonymous]

Hi,

I am using recent version. i can find only one option "Treat SAP like relational database".

I enabled it. 

Even i didnt get chance to edit it. My column is in text format , i wanted it to be date formate witout time in it. please help me with this.

One More help. How to change from import mode to direct query mode without affecting data?

Thanks 

Sai

[Anonymous]

Hi,

Are you trying to import SAP data using import mode? I am assuming you first loaded the data without transforming and still you are not able to see "Data type" in Data modelling tab even after selecting the table and column you would like to change the data type format.  Can you please let us know the error that you are reciveing.Please find the below screenshot

I don't think so we can change from import mode to direct query mode without affecting data after bulding the dashboard. We have to select this option while building the dashboard.

[Anonymous]

Thanks for replying Aswini!! It's very helpfull.

I got solution for that Date formatting.

But I wanted to know that "Is it impossible to Change import mode to direct query" or Is there any way to do that?

Actually, I built report using import mode, which i didnt notice untill i complete my report. Later, I tried changing to directquery, but i couldn't find any solution for that. 

Thanks 

Sai