Analyze in Excel not available to Users

[stetson]

I've created a workspace, consisting of a dataset and a report. I've also published an App with the same. Allow all users to connect to the app's underlying datasets using the Build permission is enabled on the app.

Users are assigned to a security group that has Contributor priveledges on the workspace. In the Admin Portal, under Tenant settings, Allow XMLA endpoints and Analyze in Excel with on-premises datasets is enabled, as is Export to Excel and Allow live connections. The Users have Power BI Pro licenses.

As an admin, I have the Analyze in excel option. However, our users do not and when they try to refresh a data source in Excel they receive a "Forbidden" error.

I've tried the suggestions listed on these pages with no luck:
Troubleshoot connecting Excel to Power BI data - Power BI | Microsoft Docs

Start in Power BI with Analyze in Excel - Power BI | Microsoft Docs

What else should I check to allow my users to connect to datasets and use Analyze in Excel option?

[v-xiaoyan-msft]

Hi @stetson ,

The issue may be caused by “swapping” between accounts and potentially the login process trying to log into the wrong account. There is a manual work around to fix this problem.

1.Navigate to the Excel file you downloaded.

2.Right click the Excel file and edit it in Notepad.

3.Find the section that starts with <odc:ConnectionString>

4.Add the following text immediately after this string

User ID =

name@youremailaddress.com; 

5.Save the file, and then double click to open it again.

You should now be taken to the correct login screen to give you access to the data.

Articles for your reference:

“The HTTP server returned the following error: Forbidden.” 

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[stetson · Accepted solution]

We created a ticket with Microsoft to investigate. The issue was that one of our security groups was deleted, and a mail-enabled security group was created with the same name. When this happened, the security group remained in-place as a Specific security group under the Allow Live Connections setting in Tenant Settings. We removed this specific security group, and then re-added it and it began to work again.

My best guess is that Power BI was trying to assign permissions to the old security group because it had the same name, but the old group no longer existed. By removing it as a specific security group and then re-adding it, Power BI was able to point the permission to the correct group.

[GilbertQ]

Hi @stetson 

Can you confirm on the actual dataset permissions that the users have build permissions?

[GilbertQ]

Hi @stetson 

Have you asked your users to try and connect directly from Excel to Power BI as shown below when using Excel?

You could also try the Excel in the web?

Create Connected PivotTables in Excel for the web | Microsoft Power BI Blog | Microsoft Power BI

[stetson]

This was a good suggestion, and it brought an interesting result. The User can see the dataset as an available option, and when they select it, it populates as a connection under Queries & Connections - however, the data never populates (no pivot table is created) and there is no response from Excel when the user tries to refresh the connection.

It may be worth noting that, as an admin, I am able to perform this as expected.