Solved: How to provide access to analyze in excel option?

Anonymous · ‎08-31-2021

A user (with Pro License), is able to export individual tables but not to use the “analyze in excel” option.

How can I provide access to “analyze in excel”?

The user is not able to do so; and gets the error:

and:

Anonymous · ‎08-31-2021

I found the answer on this post....

Analyze in Excel - 'unable to obtain list of table... - Microsoft Power BI Community

The solution is: "To fix it, I opened the source pbix file in Power BI desktop and selected Transform Data > Data Source Settings. Both the Server and the Database name were filled in. I deleted the Database name and clicked ok. This caused the Navigator screen to pop up, showing me all the databases on the server (I only have one). However, rather than just selecting the Database name and clicking ok, I dropped one level lower and selected the model/perspective name instead. I then republished the report and had no issues analyzing in Excel."

This is shocking! How come... by entering the db name manually, I am breaking the functionality??

View solution in original post

Anonymous · ‎08-31-2021

I found the answer on this post....

Analyze in Excel - 'unable to obtain list of table... - Microsoft Power BI Community

The solution is: "To fix it, I opened the source pbix file in Power BI desktop and selected Transform Data > Data Source Settings. Both the Server and the Database name were filled in. I deleted the Database name and clicked ok. This caused the Navigator screen to pop up, showing me all the databases on the server (I only have one). However, rather than just selecting the Database name and clicking ok, I dropped one level lower and selected the model/perspective name instead. I then republished the report and had no issues analyzing in Excel."

This is shocking! How come... by entering the db name manually, I am breaking the functionality??