cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nerd_in_NE
Frequent Visitor

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 via Power Query in Excel. There are a few columns in the source file with dates that use a custom date format. The 2nd step in my query, after my source step, expands the table. At this point, the dates are already showing as null. I'm having the same problem with a 2nd file that uses a custom cell format in the .xlsx source file.

 

Solutions Tried:

  1. I've turned off automatic type detection in File>Options and settings>Query Options>Never detect...
  2. I've tried converting the data to text, then number, and then date.
  3. I've tried #2 after replacing null values.

 

What Works: If I open the source file and reformat the column with the dates as general, the dates no longer show as null from the beginning.

 

What I Need: Ideally, I'd like not to have to manipulate the file each time and be able to pull the date values in automatically.

 

Please help! Any help is appreciated! 😀

 

For some reason, I can't tag the Biccountant, @lmkeF. It would be great if you could help lmkeF!

1 ACCEPTED SOLUTION

@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.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
nerd_in_NE
Frequent Visitor

@edhans & @Jimmy801,

 

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).

 

  1. Is there a way for Power Query to read those dates and/or reformat the file?
  2. 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:

  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.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Jimmy801
Community Champion
Community Champion

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

@Jimmy801 , Thank for the response!

 

@edhans , Is there a process or trick to ensure that the .xls or .csv file is saved using proper "MS Excel format standards" so Power Query doesn't struggle to read it?

Jimmy801
Community Champion
Community Champion

Hello @edhans 

 

just as @edhans is saying, the xlsx-file is probably stored in a bad way. Most of this issues are solved by opening the file one time in Excel and make a save/save as.

 

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

edhans
Super User
Super User

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors