Forum Discussion
Helper IAutomatically detect date versus datetime
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
Helper IEssentially... 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.
Community ChampionYou 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
robarivasPost Patron
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 :smileymad:
- kevlarmpowered
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.
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} ) ),
