cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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, PowerBI converts it to datetime and I have to go through and change each column back manually.  Would save me a headache if it would just understand the columns are date and not datetime from the source.

 

 

1 ACCEPTED SOLUTION

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
Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
Microsoft
Microsoft

@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,

Capture1.PNG

Capture2.PNG
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

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. 

 

 

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
Specializing in Power Query Formula Language (M)

View solution in original post

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 Smiley Mad

Hi,

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

I have exactly the same issue...

 

Thanks,

David


@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}
              )
      ),

 

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.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors