Forum Discussion

kevlarmpowered's avatar
kevlarmpowered
Icon for Helper I rankHelper I
8 years ago
Solved

Automatically detect date versus datetime

Is there anyway to get PowerBI to automatically detect date instead of defaulting to datetime for columns?  My datasource already has the data stored as date (not datetime), but when I import it, Pow...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can convert all datetime columns to date as illustrated in the query below:

     

    let
    Source = #table(type table[Date1 = datetime, Text = text, Date2 = datetime, Number = number],
        {{#datetime(2018,1,1,0,0,0),"Hello",#datetime(2018,1,2,0,0,0),1},
         {#datetime(2018,1,3,0,0,0),"World",#datetime(2018,1,4,0,0,0),2}}),
    TransformList = List.Transform(Table.ColumnsOfType(Source,{type datetime}), each {_, type date}),
    DatetimesToDates = Table.TransformColumnTypes(Source,TransformList)
in
    DatetimesToDates

     

    Should you have 1 or a few columns that must stay on datetime, you can remove those like in the query below in which "Date2" is removed (and will stay on datetime):

     

    let
    Source = #table(type table[Date1 = datetime, Text = text, Date2 = datetime, Number = number],
        {{#datetime(2018,1,1,0,0,0),"Hello",#datetime(2018,1,2,0,0,0),1},
         {#datetime(2018,1,3,0,0,0),"World",#datetime(2018,1,4,0,0,0),2}}),
    DatetimeColumns = Table.ColumnsOfType(Source,{type datetime}),
    FilteredColumns = List.Difference(DatetimeColumns,{"Date2"}),
    TransformList = List.Transform(FilteredColumns, each {_, type date}),
    DatetimesToDates = Table.TransformColumnTypes(Source,TransformList)
in
    DatetimesToDates
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

kevlarmpowered,

 

Do you mean that originally, the data type in your data source is date, however, when importing it to Power BI, it automatically gets converted to datetime format?


In that case, may I know how is your data source like and how did you set the date type column? In my environment, if I use SQL Server as the datasource, creating a date type column, when importing into power BI, there is no issue in recognizing the format. For example,


You could change the format as well to display different date rendering format.  Noted it happens the same when I use EXCEL as the datasource. That is to say, the date type can be recognized by PowerBI, but you may need to select the rendering format of the date as the screenshot above.

 

Regards,

Charlie Liao

kevlarmpowered's avatar
kevlarmpowered
Icon for Helper I rankHelper I
8 years ago

Essentially... when importing, I wrap every datetime column that is in my SQL2016 database with

 

select try_convert(date,mydatetimecolumn) justTheDate from myTable.

 

During the import, it will convert 01/10/2018 09:00:00am to 01/10/2018 12:00:00am.  I then have to go and manually change the data type from datetime to just date.

 

This would not be a big deal except the table that I am importing has 148 date columns... and that gets a bit tedious. 

 

 

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    You can convert all datetime columns to date as illustrated in the query below:

     

    let
    Source = #table(type table[Date1 = datetime, Text = text, Date2 = datetime, Number = number],
        {{#datetime(2018,1,1,0,0,0),"Hello",#datetime(2018,1,2,0,0,0),1},
         {#datetime(2018,1,3,0,0,0),"World",#datetime(2018,1,4,0,0,0),2}}),
    TransformList = List.Transform(Table.ColumnsOfType(Source,{type datetime}), each {_, type date}),
    DatetimesToDates = Table.TransformColumnTypes(Source,TransformList)
in
    DatetimesToDates

     

    Should you have 1 or a few columns that must stay on datetime, you can remove those like in the query below in which "Date2" is removed (and will stay on datetime):

     

    let
    Source = #table(type table[Date1 = datetime, Text = text, Date2 = datetime, Number = number],
        {{#datetime(2018,1,1,0,0,0),"Hello",#datetime(2018,1,2,0,0,0),1},
         {#datetime(2018,1,3,0,0,0),"World",#datetime(2018,1,4,0,0,0),2}}),
    DatetimeColumns = Table.ColumnsOfType(Source,{type datetime}),
    FilteredColumns = List.Difference(DatetimeColumns,{"Date2"}),
    TransformList = List.Transform(FilteredColumns, each {_, type date}),
    DatetimesToDates = Table.TransformColumnTypes(Source,TransformList)
in
    DatetimesToDates
    • robarivas's avatar
      robarivas
      Icon for Post Patron rankPost Patron
      8 years ago

      I have the same problem. Don't understand the proposed solution. I'm pulling some date fields from IBM DB2. Need to pull the data by providing a SQL statement (long story, don't ask). However, it adds 12:00:00 AM, which then forces me to have to change data type to Date. This is a big problem because Power Query won't fold data type conversion back to DB2 :smileymad:

      • kevlarmpowered's avatar
        kevlarmpowered
        Icon for Helper I rankHelper I
        8 years ago

        In theory when I read the code, it was supposed to work by adding those lines to the advanced query.  That being said, I could never get it to work for me.  Whenever I tried to do the list columns by type, it always returned blank.