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?
Hello nerd_in_NE
if you have a flat file (meaning only one sheet with one table in it) store the file as .csv-file then you are on the save side. Otherwise storing as xlsx-file should also always work out. (At least I never encountered something different)
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy