Fix "Analyze in Excel" report after changes to data model

[kevhav]

Some of our users have used "Analyze in Excel" to create reports in Excel that are connected to a dataset in the Power BI Service.

Some Analyze in Excel reports have been maintained and used over a long period of time. But also, I modify the dataset/data model, over time. And if I change the name of a field, or change the name of the table containing a field ... and, if an Analyze in Excel report uses one of those fields that got modified ... then it seems like the Analyze in Excel report is broken and cannot be fixed.

When trying to refresh an Analyze in Excel report after such a change was made, an error message pops up saying:

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.

And that's it. There does not seem to be any way to fix the report. I cannot get the updated field list from the Power BI dataset to show in the field list, in Excel. I've tried clicking "Refresh" in Excel, and I've tried updating the properties of the data connection to ensure the data connection is pointing to the right dataset in the Power BI Service. But nothing works.

With a Power BI report built in Power BI Desktop, such changes are easy to handle. At first, a data visualization will show an error like "Something's wrong with one or more fields. See details" ... and there is a button that says "Fix This" ... then, I can identify and remove the fields that are no longer valid ... and re-add the new fields with the new names. 

So, is there some way to handle it with an Analyze in Excel report? Some fix that I haven't figured out? What do you do?

I hope we don't have to re-create the Analyze in Excel report from scratch, whenever a field changes -- that is the only option I've found, so far!

[YunNC]

I'm having this issue as well. A comprehensive analysis was created off of the data model using Analyze in Excel function. And then when we made change to a field in the Power BI data model, published. Bang, the error showed up and whatever we do, nothing will solve it. The old field continues to exist even though the model has been refreshed to the latest. Can we look into this? The Analyze in Excel function is so amazingly helpful and is the sole reason why we chose this over Tableau. But some of this inconvenience is creating frustration for end user adoption. Please advise!

[GilbertQ]

hi there

What happens if before you refresh the data you remove the field that has changed?

[kevhav]

@GilbertQ, thanks for the suggestion.

In the example I have, the pivot table in the "Analyze in Excel" file includes multiple fields that have been renamed. When I attempt to remove one, I just get the same error and nothing changes.

I do wonder if your suggestion would work if only a single field had been renamed. But I don't have an example like that.

And as far as I can tell, I can only attempt to remove the fields one a time. I don't see a way to attempt to remove them all at once.

[GilbertQ]

Hi there

Yes I think you would have to remove them one at a time and then add them back in again?

[YunNC]

Tried this. I took out the old field, refreshed the data. The new field still doesn't show. But if I insert a new pivot table from external connection, that new field (renamed field) will then show up. So some kind of cache is at work here but I can't figure out how.

[kevhav]

But I cannot remove them one at a time. When I attempt to remove one, I just get the same error and nothing changes.