Forum Discussion
Default "Changed Type" in Excel
In Power Query for Excel, the default behavior for the automatically generated #"Changed Type" step on my system is to set columns with dates to type datetime if the data source is in the open Excel workbook.
If the datasource is a saved Excel workbook, the same data will be set to type date in Excel.
Is there any method of changing this default behavior in Excel for a data source within the open Workbook?
(I am aware of how to change it manually; I would like to change the default behavior).
Data Source
Data > Get&Transform > From Table >
Automatically generated code from the above
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type datetime}, {"DateTimes", type datetime}})
in
#"Changed Type"
Yes you are right. Currently, Power Query automatically converts columns from Excel.CurrentWorkbook() to type datetime, even when the underlying data is a plain date. This is inconsistent with how external Excel file sources (Excel.Workbook(...)) behave, which correctly set columns to type date when appropriate. This behavior creates unnecessary cleanup work when working with tables in the current workbook. It would be incredibly helpful if users could globally configure the default data type mapping for date/datetime detection or choose to disable or override the Changed Type step defaults for Excel.CurrentWorkbook(). Please consider adding a setting (in Power Query or Excel Options) to control this behavior.
The final option:
There is no longer a dedicated Power Query UserVoice, but you can use Microsoft Excel Feedback Portal:
https://feedbackportal.microsoft.com/feedbackGo to Excel > Power Query, and click + Give feedback
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
4 Replies
Hey ronrsnfld ,
You're absolutely right in your observation Power Query for Excel behaves differently depending on whether the source table comes from:
- an open Excel workbook via Excel.CurrentWorkbook()) have default Changed Type to datetime, even plain dates become datetime.
- a saved workbook file via Excel.Workbook(File.Contents(...)) have default Changed Type to date, even plain dates become date.
There is currently no built-in setting in Power Query or Excel to change this default behavior globally. However, you have some options:
1. Disable Auto Type Detection (Prevent the "Changed Type" Step)
- Go to Power Query Editor
- Click on File > Options and Settings > Query Options
- Under Global > Data Load, uncheck
Automatically detect column types and headers for unstructured sources. While this stops auto-type detection for unstructured sources (like text/CSV), it does not affect Excel tables, which are considered structured. So this workaround has limited effect on open workbook sources.
2. Use a Template with a Custom "Changed Type" Step
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}, {"DateTimes", type datetime}})
in
#"Changed Type"
3. Use M Code to Convert DateTime to Date Automatically
After the "Changed Type" step, insert a step to convert specific columns back to type date:#"Converted Dates Column" = Table.TransformColumnTypes(#"Changed Type", {{"Dates", type date}})
You could use this as a reusable function or partial query step.If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Nasif_Azam wrote:There is currently no built-in setting in Power Query or Excel to change this default behavior globally.
Unfortunate. Yes, I am aware of all the various work-arounds, including writing my own type detection routines, but was hoping to avoid that. Do you happen to have the link where I can request to have this added as a feature?
Yes you are right. Currently, Power Query automatically converts columns from Excel.CurrentWorkbook() to type datetime, even when the underlying data is a plain date. This is inconsistent with how external Excel file sources (Excel.Workbook(...)) behave, which correctly set columns to type date when appropriate. This behavior creates unnecessary cleanup work when working with tables in the current workbook. It would be incredibly helpful if users could globally configure the default data type mapping for date/datetime detection or choose to disable or override the Changed Type step defaults for Excel.CurrentWorkbook(). Please consider adding a setting (in Power Query or Excel Options) to control this behavior.
The final option:
There is no longer a dedicated Power Query UserVoice, but you can use Microsoft Excel Feedback Portal:
https://feedbackportal.microsoft.com/feedbackGo to Excel > Power Query, and click + Give feedback
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Yes, you can handle this behavior by modifying the time part of the values in the "Changed Type" step. While there isn’t a built-in setting in Excel Power Query to change the default type detection behavior directly, you can programmatically adjust the results after the "Changed Type" step.
If you find that Power Query assigns a datetime type but the time part is always 00:00:00, you can convert those columns to date using a conditional step. For example:
