I've been experiencing inconsistent data import from Excel files in SharePoint and was wondering if anyone else has experienced the same and if so, any suggested resolutions/fixes?
Due to confidentiality and security restrictions around the data I cannot include screenshots but I will try to explain as best I can.
One of my Power BI reports pulls data from 25+ Excel files stored in the same SharePoint site. The Excel files contain macros and most cells are protected to ensure users only update specific fields. Most of the data doesn't have any issues with pulling into Power Query, however from time to time, specific data is not imported and shows as blank values against that file.
For example, File A, File B and File C are all identical in layout, formulas and macros. The content within the files refers to different business units and is update by different individuals. There are multiple sheets within each file. One sheet contains Status Update, Next Steps and Financial Comments fields, all of which are text format. File A and File B import into Power Query without issue, File C has a row to indicate it exists, but the Status Update, Next Steps and Financial Comments data is completely blank even though it is there in the file. The other worksheets have imported correctly.
To date I have been unable to identify a specific cause for this issue. Discussions with our IT department have been inconclusive as well. They are adament it is not SharePoint or MS Teams related and must be Power BI related. As it is not restricted to the same file, the verdict is it isn't MS Excel either.
To resolve this blank data issue, the file needs to be checked out, opened in desktop app, saved and checked back in. Then the data in the semantic model is refreshed and the missing data will be present. Because of this users are asked to check the file out and open in desktop app to view or make changes.
I cannot see anything in Power Query that will resolve this as it is not consistently the same files. Enable Load and Include in Report Refresh settings are both active for the query. I've tried creating a completely new Power BI file in case the query was corrupt, same result. Same result using the Desktop app and in the Power BI App Premium space.
Any suggestions / feedback would be appreciated.
