Excel’s PowerQuery gets data type conversion options

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address