Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aarikc17
Advocate I
Advocate I

Date Format Direct Query

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.

1 ACCEPTED SOLUTION
19 REPLIES 19
rajBi11
Regular Visitor

WannesLimbourg
Frequent Visitor

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

RedMax
New Member

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
Not applicable

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
Not applicable

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.

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
Community Support
Community Support

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.

7.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

@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.

 

2017_07_21_06_35_24_Eric_s_SOL_Attempt_Direct_Query_Power_BI_Desktop.png

 

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

 

2017_07_21_06_36_44_Date_and_Time.png

 

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

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.

djg1kor_0-1646722148697.png

 

dtartaglia
Resolver I
Resolver I

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

2017_07_19_16_17_51_Eric_s_SOL_Attempt_Direct_Query_Query_Editor.png

 

After changing the data type

2017_07_19_16_18_33_Eric_s_SOL_Attempt_Direct_Query_Query_Editor.png

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

aarikc17 are you on the lastest version of BI Desktop?

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
Not applicable

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
Not applicable

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 screenshotPlease 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
Not applicable

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 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.