Forum Discussion
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.
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 DatetimesToDatesShould 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
8 Replies
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
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 DatetimesToDatesShould 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
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.
