Excel’s powerful data import feature is finally getting some control over the automatic conversion of incoming info into Excel data types.
When Excel 365’s Get and Transform feature imports data, it looks at each field (column) and tries to work out what type of data it is. That’s for unstructured data (like text files) which don’t come with details of what each field (column) is supposed to contain.
Text, Number, Date etc; Get and Transform mostly guesses correctly but not always.
Now, Excel Query Options (under Data | Get Data) include a choice
Type Detection
- Always detect column types and headers for unstructured sources
- Detect column types and headers for unstructured sources according to each file’s setting (default)
- Never detect column types and headers for unstructured sources
For most the default choice is enough but it’s nice to either force detection or turn it off entirely.
Changing Data Type
Happily, PowerQuery lets you change a data type and repeat that change next time data is imported from the same source. Under Transform there’s a pull-down list of data types which is more complete than in Excel itself (i.e. Duration, Date/Time/Timezone, True/False).
Get and Transform is a lot more reliable than just opening a data file (like a .CSV) where Excel can wrongly change data type according to each cell contents.
Scientists still don’t know how to use Excel
PowerQuery mystery – how to Remove Duplicates, keeping most recent record
Excel 365’s data sources now include dynamic arrays
