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
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.
Solved! Go to Solution.
@Anonymous ,
While connecting to direct query mode you can use native query editor to transform your columns.
CONVERT(datetime, YourDatecolumn)
Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!
@Anonymous ,
While connecting to direct query mode you can use native query editor to transform your columns.
CONVERT(datetime, YourDatecolumn)
Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!
@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..
User | Count |
---|---|
161 | |
111 | |
96 | |
87 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |