Excel Erroring Upon Refresh of Queries and Tables

[PowerBI_Chaos]

Hello,

Need help/advice.

We have a folder full of excel documents used by our project managers to enter data for and report on their projects.  Our PowerBI environment pulls data from all these excel documents through a dataflow and into our portfolio reporting app.  However, there has recently appeared a critical error occurring in some of the Excel documents, however it is strange.  These excel documents serve as a central source of project data.  Data is pulled into them by PowerQuery on actual financials tied to their project (from our SAP snapshot), and data is pulled from these documents for the portfolio (forecasted spend items).  Here is what we know...

Upon a data refresh (refresh OR refresh all) of actuals query for the project (via PowerQuery, and into a table) the final prompt appears when the PM attempts to save the file.  This is occurring on numerous projects, in their project documents, as well as in the template that we give them to start a new project.

The challenge is that Excel does not indicate where or what the error is.  When I open Powerquery I see no issues in the queries or steps, all steps load within powerquery just fine, and as intended.  

When the refresh is ran the table which holds the powerquery data in the document does load the new values!  The error only appears the moment you attempt to save the document.

Any advice that could put me on the right course to solve for this?  Is there any method known to identify the sheet/formula/cell or table causing the error to occur?

[Anonymous · Accepted solution]

Hi @PowerBI_Chaos ,

Please try some of the methods in this thread and check if they help with your problem:
Microsoft should FIX - "Errors were detected while saving (filename)" - Microsoft Q&A

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[PowerBI_Chaos]

Looks like there are some interesting solutions in there.  We ended up going through each of the erroring files and recreating them from a earlier template that didn't have the errors and this seems to have resolved it, for now.  Thank you for your help!