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
DeepEureka's avatar
DeepEureka
Frequent Visitor
5 years ago

Hi,

Could you find a way to transform datatime to date type without preventing query folding?

I have exactly the same issue...

 

Thanks,

David

MednaxKevin's avatar
MednaxKevin
Frequent Visitor
5 years ago
DeepEureka wrote:

Hi,

Could you find a way to transform datatime to date type without preventing query folding?

I have exactly the same issue...

 

Thanks,

David

I ended up doing it after the import in a PowerQuery step... because even if you do it in SQL with a try_convert(date, column) as _dateOnlyColumn, P BI still interprets it as datetime.  So I use the below to automagically convert all the datetimes to date in the model.

 

#"List of Columns with DateTime" = Table.ColumnsOfType( Source, {type nullable datetime}),
    #"Convert Date" = 
      Table.TransformColumnTypes
       (
          Source, 
          List.Transform
              (
                  #"List of Columns with DateTime",each {_, type date}
              )
      ),