Forum Discussion
Super UserDefault "Changed Type" in Excel
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
Super UserHey 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
Super User
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?
- Nasif_Azam
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