Forum Discussion
Source Date Data Showing as Null in Power Query in Error
nerd_in_NE - there are issues with XLS files. One example is here but I've seen two others in recent months. Power Query cannot change the files - it is query only - and cannot reliably get info if the formatting is off in the file in some way. You have two choices:
- Save as CSV
- Save as a true XLSX file. Unfortunately, many ERP exports do not follow the MS file spec, so while it can look like an Excel file to Excel, it may not to other tools like Power Query because the vendor has not adhered to the rules of an XLSX file structure.
Thanks for the quick reply!
I believe that the client's exports from 2 different systems are outputted as .xls files. The client then saves those files as .xlsx files. The client is using Excel for Mac, and Power Query currently only works with .xlsx files on Excel for Mac (unless I'm mistaken).
- Is there a way for Power Query to read those dates and/or reformat the file?
- Alternatively, is there specific save process for the client to save the .xls file in .xlsx format that allows for Power Query to connect to it and read the dates properly?
nerd_in_NE - there are issues with XLS files. One example is here but I've seen two others in recent months. Power Query cannot change the files - it is query only - and cannot reliably get info if the formatting is off in the file in some way. You have two choices:
- Save as CSV
- Save as a true XLSX file. Unfortunately, many ERP exports do not follow the MS file spec, so while it can look like an Excel file to Excel, it may not to other tools like Power Query because the vendor has not adhered to the rules of an XLSX file structure.