Analyze in Excel - Forbidden error

[elevateyourself]

Hi All,

I am trying to run an established excel report which has embedded Analyze in Excel Pivot tables and when I try to refresh them I am getting a "Forbidden Activity" error. I have read other posts regarding this issue but none of them seemed to fix whats going on here. I have full access to the Power BI report on the browser and can even create my own Analyze in excel files using the same report. When I try a refresh in the established excel report, I get the Azure Authentican window that pops up, but when I log into that with my credentials I still get the error. 

Any suggestions?

Much appreciated!

[Venkateshkk]

Yes agree, new excle sheet with new connection works well when we have the 1 or 2 reports, but if we have more reports then it is a tedious job to create reports. I also encountered the same issue. 

Change the dataset id in the excel connection properties resolves the issue. Reason for this issue given below. 

Scenario:

1) if there is already a dataset (namely ABC) created/publihsed in service, excel connection and reports are created on top the dataset.

2) Later, deleted the dataset and create a new dataset with the same name (ABC) still it wont connect, because the intenal dataset id differes from old and new ones. Dataset id can be seen inthe URL with GUID (next part of /datasets/----/)

solution:

Copy the dataset id from the URL and change it on the Excel connection properties of Initial Catalog (There will be a GUID here as well). Then it works..

[Krichardson2977]

Hello, I came across your post recently as I have been trying to figure out the same issue and can't figure out how to get past the forbidden error message when trying to refresh my pivot table from power BI. I don't want to have to create a brand new "analyze in excel" file because I have a bunch of different views saved. I want to just be able to refresh my current pivot tables. You mentioned copying over the connection string. Can you explain in greater deal how to do that? Thank you!

[collinq]

Hi @elevateyourself ,

DO you ahve more than one set of Power BI credentials?   You may be signed into Power Bi with a different set of credentials.  Also, you may need a newer version of Excel (just make sure that you ahve the latest and greatest version).  If this was working and "nothing changed" and now it does not work I would also wonder if your organization's Group Policy has changed, or your password has expired, or the network has been modified to have certain ports/links not to be allowed or your permissions were changed (you need to have underlying dataset permissions I believe).

Let us know if any of those resolve the issue.

[elevateyourself]

Hi @collinq

Thanks for posting a response! I only have 1 set of credentials so that shouldn't be an issue. My organization is not on the latest and greatest excel update, however I have collegues using the same version I have and they are able to refresh the report. I am a new user to the report so it isnt a case where it just stopped working one day. I do have access to the underlying dataset, and can freely create/use new analyze in excel files using the same source. This report was made before I was part of the company, so I am wondering if that could be an issue?

Really appreciate the help!

[collinq]

Hey @elevateyourself ,

If it works for others and not you then it does appear to be an issue with your userid.  I am not sure where the permissions issue may be though - are there mutliple data sources/files and one of them might be in a location (network share, etc) that you don't have permission to interact with and can read only?

[elevateyourself · Accepted solution]

Hi All,

I was able to solve the issue by creating a fresh Analyze in Excel file using the same Power BI report and copying over the connection string from that file into the existing excel report. I am now able to refresh and this hasnt affected the other users. 

Thanks for the help!