Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

8 REPLIES 8
v-caliao-msft
Employee
Employee

@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

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. 

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)

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.