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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Convert Text to Date in Direct Query mode

Hi, I'm using DirectQuery mode for my report. I have date fields which are of the data type Text.

 

I tried converting it into Date by changing the Data Type from Text to Date. It says "This step results in a query that is not supported in DirectQuery mode." I get the same error when I try to add a custom column (Month = Date.FromText([DateField])).

 

How do I get the month number, month name, and year from the text field? How do I convert the text field into a date field in DirectQuery mode? The existing solutions do not seem to work in DirectQuery mode.

 

Please help! Thanks.

1 ACCEPTED SOLUTION
Tahreem24
Super User
Super User

@Anonymous ,

 

While connecting to direct query mode you can use native query editor to transform your columns.

CONVERT(datetime, YourDatecolumn)

 

Capture.JPG

 

Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

2 REPLIES 2
Tahreem24
Super User
Super User

@Anonymous ,

 

While connecting to direct query mode you can use native query editor to transform your columns.

CONVERT(datetime, YourDatecolumn)

 

Capture.JPG

 

Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

@Tahreem24Thank you!

 

For others having the same issue,

Use the following SQL command while getting data for your report:

SELECT CONVERT(date, [DateSubmitted]) as [DateSubmitted]
,DATEPART("MONTH", [DateSubmitted]) as [Month]
,DATEPART("YEAR", [DateSubmitted]) as [Year]
,(DATEPART("WEEK", [DateSubmitted]) as [Week]
,'Q' + CAST(DATEPART("QUARTER", [DateSubmitted]) as varchar) as [Quarter]
FROM..

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.