Analyze in Excel – How to Deal With an XML Parsing Error

[monaraya]

I have worked on an issue related to the Analyze in Excel feature of Power BI.
For an introduction: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel

The user had access to the report and clicked on “Analyze in Excel” in the Power BI service. The downloaded .odc file opened in Microsoft Excel and showed the below error:

Usually, when this error occurs – it refers to a special or illegal character and we start looking for that one culprit.

However, in case of Power BI, I found out that this error pops up for a whole different reason.

To explain the scenario better:

• The user was able to view the .odc files of other reports successfully with no errors. Only 1 specific report showed this error.
• We tried this errored .odc file with other users in their organization and found out that a few sets of users were able to view, whereas the rest of the lot wasn’t able to.

Initially, it was quite difficult to find what was the cause of this issue, whether it was report or machine or user-specific was a big question.

We captured multiple traces, made changes in the .odc file, updated Excel and its driver versions, used Power BI add-in within Excel and nothing seemed to help.

Cause & Resolution:

In the final stage, we compared 2 reports for the same user in the machine – with a working & non-working report.

It all came down to the “Build permissions on the dataset” for the specified report.
The report was hosted in a Premium capacity and it was distributed to this user via Apps as he was a free user. During the distribution, there was no build permission provided to the underlying dataset for this user.

After providing the permissions over the dataset – the analyze in Excel worked!

Author: Sheetal Shetty

Reviewer: Mounika Narayana Reddy