Forum Discussion
Date column all null
Anonymous Omid_Motamedise PhilipTreacy
Thank you for the suggestions. While trying some of your suggestions i stumbled across some more information.
This query is automatically email out from our systems every day. The query comes as a .xls 'Microsoft Excel 97-2003 Worksheet' and when put directly into power query as is, the accounting date column shows 'null'. However when i open the Query in excel, make no changes at all but simply save it as is, i am prompted with a message i have screenshot.
Clicking continue saves the file as is. Then loading the same file back into power query, the accounting dates appear as they should be.
You may be able to see my problem already. I do not want to have to manually save this report everyday. I just want Power BI to be able to accept it as is and automatically update my report. Do you have any suggestions how i can accomplish this? What exactly is the issue with the original format? If i can have the report sent out a different way/format, what should i tell IT i need? Or maybe, is there something i can do in power query to fix this report automatically instead of resaving it in excel?
I'd suggest IT start with using a file format that isn't 20+ years old from a version of Office that is no longer supported, and hasn't been for about 10 years.
If you are loading this data from XLS files, why does the data need to be in an XLS (or XLSX) format? I'm assuming such files are just an intermediate format before loading into PBI. If so, can't it be exported to a CSV (or XLSX)?
Phil
