Convert string Date Value to Date Type (using Direct Query)

[SachinC]

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.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[SachinC]

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.

[SachinC]

Hi all,

Any news or solution to my query please?
Thanks!

[v-juanli-msft · Accepted 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

For better performance, you could change format in sql server side.

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.

[SachinC]

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!

[v-juanli-msft]

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.