Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I tried this and it wants to convert the Dataset to mixed mode - Import and Direct Query.
I want this to be Direct Query only.
My date value is: 2019-12-30T00:00:00
Please help!
Thanks.
Solved! Go to Solution.
Hi @SachinC
As tested, it doesn't support to convert text to date format in Power query when connection type is direct query.
You could add a calculated column on report view, or change date format in sql side.
Column =
VAR t_number =
FIND (
"T",
[date],
1,
0
)
VAR date_text =
LEFT (
[date],
t_number - 1
)
VAR year_t =
LEFT (
date_text,
4
)
VAR first =
FIND (
"-",
date_text,
1,
0
)
VAR second =
FIND (
"-",
date_text,
first + 1,
0
)
VAR interval = second - first - 1
VAR mm =
MID (
date_text,
first + 1,
interval
)
VAR dd =
RIGHT (
date_text,
t_number - second - 1
)
RETURN
year_t & "/" & mm & "/" & dd
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
My report uses Direct Query connecting to a MS SQL Server.
My datevalue is in the format: 2019-12-30T00:00:00
My datevalue is of type: Text
I want to convert this to type: Date.
When I do this; it comes up with: "This step results in a query that is not supported in DirectQuery mode." Button: 'Switch all tables to Import mode'
I do not want to make this Import Mode or Mixed Mode (Import Mode and Direct Query). I want to remain in Direct Query mode.
I hope this explains?
Please help.
Hi all,
Any news or solution to my query please?
Thanks!
Hi @SachinC
As tested, it doesn't support to convert text to date format in Power query when connection type is direct query.
You could add a calculated column on report view, or change date format in sql side.
Column =
VAR t_number =
FIND (
"T",
[date],
1,
0
)
VAR date_text =
LEFT (
[date],
t_number - 1
)
VAR year_t =
LEFT (
date_text,
4
)
VAR first =
FIND (
"-",
date_text,
1,
0
)
VAR second =
FIND (
"-",
date_text,
first + 1,
0
)
VAR interval = second - first - 1
VAR mm =
MID (
date_text,
first + 1,
interval
)
VAR dd =
RIGHT (
date_text,
t_number - second - 1
)
RETURN
year_t & "/" & mm & "/" & dd
Hi Maggie,
Thanks very much indeed - that worked a treat.
However, I want to use the Slicer visualisation, therefore I created a new column with: Format([newdatecleanedup],"mmm/yyyy") but that didn't play ball.
Please advise?
Thanks!
Hi @SachinC
It doesn't support to use "Format" in direct query mode.
You can change the format of the date column to "mmm yyyy".
when adding dates to slicer, it only shows mm/dd/yyyy format by default.
Or you can create columns
month name = SWITCH(MONTH([date format]),1,"Jan",2,"Feb",3,"March",4,"Apr",5,"May",6,"Jun",7,"July",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec")
year = YEAR([date format])
mmm yy = [month name]&"/"&[year]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |