Forum Discussion

kevlarmpowered's avatar
kevlarmpowered
Helper 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
Microsoft 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

glpelletier's avatar
glpelletier
Regular Visitor
3 years ago

Hello, I do have the problem you are describing.

In sql, my date field has a date format in the table. The field is extracted in a view and keeps it format (YYYY-MM_DD). In Power Query, the field is imported as text format. If I go under the Transform tab and detect format, it changes the format to datetime.

I would like to avoid manually changing the format to date in Power Query. I could change it to date, but I would have to add a conditional statement as sometimes that column is not present.

It's the first time it happens to me that the format is not detexted correctly by Power Query.

Thank you in advance.