Forum Discussion

nerd_in_NE's avatar
nerd_in_NE
Frequent Visitor
5 years ago
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...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    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:

    1. Save as CSV
    2. 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.
edhans's avatar
edhans
Community Champion
5 years ago

nerd_in_NE are you 100% sure these are XLSX files, or could they be XLS (Excel 2003/2007 format) files? I have seen more and more issues where formatting in XLS files cause issues in Power Query. 

Also, could they be truly 2003/2007 XLS files that some external system is exporting as XLSX? There are a ton of ERP system that do a horrible job confirming to MS Excel format standards, and the file opens ok in Excel, but outside systems, like Power Query, can choke on it.