cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcresp
Advocate IV
Advocate IV

Disable Analyse in Excel

We are about to launch a Power BI Dashboard to external users and I have just realised that they will be able to use Analyse in Excel. It is not ideal that they can use Export but having external users being able to use Analyse in Excel gives them far to much access to the data and will mean that we would not be able to laucnh. Does anyone know if there is a way to turn this off? I know that you can turn off export but this stops export working in all instances which is not what we really want. However, if useing the admin disable export will disable analyse in Excel that might be the only option. 

 

There must be some way to reslove this. Analyse is Excel is a great feature for internal users but certainly not somthing that you would want external users to be able to gain access to.

 

Any help on this would be greatly apreciated as this has thrown a spanner in our plans to launch.

 

Thanks

 

David

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

 

I can confirm that I now tested removing the ability to Export Data, as well as "Allow users to use Analyze in Excel with on-premise datasets" and waited 10 minutes.

 

When I went back in, all the options to Analyze in Excel were removed.

 

Below is what my settings were:

Admin Portal - Disable Exporting and Analyze in Excel.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

12 REPLIES 12
todo-claro
Regular Visitor

Hello All,

 

there is a workaround for this on Dataset Level Withot being Andmin or Tentant, and not for all the Organization.

If you hide all (or certain) tables for the report view (right click on table in Data or Relationship view in power BI desktop, then select Hide in Report View, then publish again. ), then Analyze in Excel will not export this tables. 

 

Disadvantage: in Power BI desktop, in order to futher develop the model, you will need to unhide the tables again. 😞

This is a information security issue as well!

 

Think about a huge dataset that potentially covers a lot of use cases and that is used for a report that only uses a small part of that big model. Sharing this single report will expose all data to be analyzed and loaded into Excel. That's not only an issue for external users but vor internals as well because it circumvents permissions a report creator might have in mind when explicitely not including a field into his/her report!

 

As a report creator we need to explicitly allow this feature to be availlable to the consumers for each report individually! To support the according idea, please vote for it here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/34107790-publish-to-app-restrict-...

We seem to have found a workaround for this:

 

  1. In Power BI Desktop, click data on the left:
    1Data.png
  2. In the bar on the right-hand side of the data screen, for each table, select hide in report view:
    2Hide.png
  3. Click report on the left to return to the report view:
    3Report.png
  4. Optionally, in the bar on the right-hand side in report view, you can choose to view hidden tables (this will not affect the online report, only while working in PowerBI Desktop):
    4ViewHidden.png
  5. Publish this version.

 

 

 

Now, users in our workspace can still export summarized Excel Data (as we want):

ExportExcel.PNG

 

But they cannot view/export the underlying data - nor via the Excel download option shown above, nor via the "Analyse in Excel" link topright. The "Analyse in Excel" link is still there, but only opens an empty Excel file:

5Analyse.PNG

 

 

(EDIT: now I see that todo-claro already mentioned this same workaround already on the previous page. Thnx todo-claro :))

 

Still, I hope Microsoft will simply add a "turn Analyze in Excel on/off" button, while keeping the functionality to export summarized data to Excel.

@Barendnu: Thanks for your reply! Yes, I know about hiding fields and it might help in certain scenarios.

However, that approach cripples the use of "Power BI Live Connect" as well!

We make extensive use of that feature and build big datasets that are used multiple times in reports. While you must be member of the workspace to access the dataset with live query, Analyze in Excel offers the same capabilities when the report is simply shared.

GilbertQ
Super User
Super User

 

I can confirm that I now tested removing the ability to Export Data, as well as "Allow users to use Analyze in Excel with on-premise datasets" and waited 10 minutes.

 

When I went back in, all the options to Analyze in Excel were removed.

 

Below is what my settings were:

Admin Portal - Disable Exporting and Analyze in Excel.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thank you very much @GilbertQ this has worked for us as well. We tried turning off Analyse in Excel but you are correct that you need to turn off teh export feature as well. This is an overly blunt tool as it has now turned both features off for all dashboards which is far less than ideal but we at least have some control. Thank you very much for letting us know the solution.

 

Regards

 

David

It was great to find a way to turn off analyse in Excel but the issue is that turning it off disables a major part of Power BI that we were planning to be able to use for internal users. The way of turning analyse in Excel and export off is currently far too blunt and we really need to have this control at a dashboard level. 

 

The other option is that we move to Power BI Embeded for external users which has some advantages and is an options that we are pursuing. The only issues with this is that I have not been able to find any way to trurn off export in Power BI Embeded. Does anyone know if there is anyway that export can be turned off in Power BI Embeded.

@dcresp have you validated that the setting in the Power BI Admin Portal "Allow users to Analyze in Excel with on-premises data sets" is turned to "off" - I haven't tested this setting out, but it seems like it may turn this off.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks @Seth_C_Bauer I tried this. This does not seem to work for external users. In fact it does not seem to have worked even for internal users. I expect the issue is that it is a dataset that is not getting refreshed automatically as I do not want external users to have to have Power BI Pro.

 

I am starting to wonder if the only answer to this is to move to Power BI Embedded. Somthing that we were not planning to do just yet.

 

Regards

 

David

Hi dcresp,

 

 

Based on what I know, Analyze in Excel would only be available under dataset, sharing dashboard won't share the dataset, and exporting data from Reports would only gain access to the data shown in the reports.

 

By the way, currently it has no available way to disable Analyze in Excel option (Edited, I mean in addition to the method through Admin Portal), you may consider submit an idea on this topic, to allow it work without disable Export data.

Power BI Ideas

Regards

 

Thanks for the responses. I have established that there is no way to turn off Analyse in Excel which I see as a huge oversight by Microsoft as it gives external users full access to all the data in the data model behind the Power BI report. We are now moving to set up a Power BI embedded structure.

how about Publish To Web?

 

when in Service, select the Report (not the dashboard) and then in the upper frame on left is the 'File' options - one of which is Publish to Web. 

 

That will give you a URL you can send to external users that does not allow them into the data set.

 

There is of course other issues in terms of not having any sort of log in controls to it ..... but it is a consideration perhaps....

 

www.CahabaData.com

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors