Forum Discussion

SachinC's avatar
SachinC
Helper V
6 years ago
Solved

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

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. ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
SachinC
Helper V
6 years ago

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's avatar
SachinC
Helper V
6 years ago

Hi all,

Any news or solution to my query please?
Thanks!

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      SachinC
      Helper V
      6 years ago

      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's avatar
        v-juanli-msft
        Community Support
        6 years ago

        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.