Forum Discussion
Solved
Source Date Data Showing as Null in Power Query in Error
Problem: Valid dates from the source show up as null in Power Query at the very beginning of the query. Situation: I'm working on a project that pulls in data from an Excel (.xlsx) source file vi...
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.
Unfortunately no nerd_in_NE - you are at the mercy of the tool exporting the file. I've never seen issues with CSV/Text files, but XLS is a problem. The fix is, unfortunately, open in Excel, and force it to save as XLSX. Excel will fix any issues.
You could automate that via a Power Automate flow though if you are good with that tool.
Please mark one of these as the solution if it helps, and give thumbs up to anyone that has helped. Thanks!