Forum Discussion

eddd83's avatar
eddd83
Resolver I
6 years ago
Solved

Direct query - Remove time for date column

I am doing a direct query and in my SQL query, i'm converting a column to a date using convert(date, COLUMN). However, the data is imported into powerBI as "3/9/2020 12:00:00 AM". Since I am using a direct query, I can't use power bi to transform this column. 

 

Is there any way to show only the date (3/9/202) and to cut out the time (12:00:00AM)?

 

Thanks

 

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi eddd83 

    yes, just pick the column, go to the Modeling (or Column tools) ribbon and select date format as you wish

     

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi eddd83 

    yes, just pick the column, go to the Modeling (or Column tools) ribbon and select date format as you wish

     

    • eddd83's avatar
      eddd83
      Resolver I
      6 years ago

      az38 What you suggested doesn't work because In the modelling tab, it says "This table uses DirectQuery and cannot be shown"

       

      • eddd83's avatar
        eddd83
        Resolver I
        6 years ago

        az38 Nevermind. I see what you meant and I figured it out

         

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Hello Syndicate_Admin

    I guess you already found out by the date of your reply. I have had the same problem with DirectSQL and I have managed to solve it without getting the famous message of "This table uses DirectQuery and you can not ..."

    The fact is that this message does not come out when you DUPLICATE the column, but when you TRANSFORM to "date only" format. Therefore, what I have done to achieve this is to DUPLICATE the column (in my case SeatDate) and renamed it to SeatDate - DDMMAAAA.

    Then, outside the powerquery editor, in the PowerBI Desktop editor, I click right on the column created SEATDATE - DDMMAAAA and above all, in format, I scroll and select in "Common Formats" the second option, which is called (ShortDate).

    In this way I have managed to get a date that is, for example, "23/04/2021 13:30:45", to a date that remains only "23/04/2021". Thus allowing you to continue with your analysis.

    Sorry for the delay, I started with PowerBi recently.

  • Dan_Klunk's avatar
    Dan_Klunk
    Regular Visitor
    1 year ago

    Create a new column "= DATEVALUE(..." and reference your Date/Time column within that new column. This removes the time and, in our case, allowed the grouping by only the Date (without the Time causing separate rows for each separate Time value).