I think I now got this question 4 times in the last months, so I thought I'd write it down so I can reference it later, and point people to it 😄
What are the requirements so (a group of) colleagues can start using Analyze in Excel?
Good question, let me break it down.
In general, I think it's also better to use Analyze in Excel than Export to Excel!
Reza Rad also wrote about why that's important earlier.
Prerequisites to use Analyze in Excel
- Power BI license
You either need a Power BI Pro or PPU license, or the semantic model you connect to must be in a workspace backed by a Power BI Premium / Fabric F-capacity, this needs to be an F64 or higher SKU. Although I have seen some instances where it might have (temporarily) worked with a lower F-SKU, this is not supported/allowed (see Power BI licensing). - Tenant Setting
Your Power BI / Fabric Administrator needs to enable the Tenant setting in the Admin Portal: "Users can work with Power BI semantic models in Excel using a live connection". You can learn more about that setting on the admin portal documentation. - Semantic Model permissions
The user needs at least build permissions on the semantic model, or at least the Contributor role in the workspace. Ideally you put people in an Entra ID group and give that the appropriate permissions. - Excel
You obviously need Excel Desktop or Excel for the web installed for it to work. You might have to install some updates for it to work.
A few things to be aware of:
- Analyze in Excel creates a dynamic, live connection to the Power BI dataset, so any changes to the dataset will be reflected when the Excel report is refreshed
- Free users can analyze data from Premium workspaces without needing a Pro license, provided they have the appropriate role (at least build permissions or Contributor role) assigned in that Premium workspace
- You'll need to use measures in the PivotTable's values area, as you can't directly drag numeric columns like 'cost' into that area. It's anyway better to create explicit instead of implicit measures
Hi -
ReplyDeleteI'm having some trouble setting up Analyse in Excel so that users with free accounts can refresh the Excel sheet. The underlying model is in a Fabric capacity workspace, and the users have build permission on the model. They can view the Excel sheet and see the data, but cannot change any filters on any pivot table or refresh the data without running straight into an error: "To refresh these semantic models, you must have permission. Request access and try again..."
I've been trying different combinations of permissions all morning and I can't seem to get the refresh to work with a free account. It works fine with my Pro account. I note that the docs (https://learn.microsoft.com/en-us/power-bi/collaborate-share/office-integration/service-analyze-in-excel) say that it should work with a free account. I've tried creating a new Analyse in Excel sheet and copying the connection string from that one to the old one, but that didn't help.
Any advice is appreciated.
Hi Adam, I can't make it up from your comment, but is the Fabric capacity F64 or higher? Otherwise it won't work.
ReplyDelete