Recently, I posted a poll on Twitter to vote for new blog topics. One thing that popped-up in my mind, was more on the governance side of Power BI. A topic where I spend a lot of my day-to-day time, but is maybe less interesting for others? At least that is what I thought. Below the results.

Governance is a new topic for me to write about and less technical than I’m used to. New to this topic? Governance can more less be described the guidelines and settings on a higher framework level that defines what you can and what you cannot do with a specific product or service. It enables the organization in managing risks and ensure that the activities done are in line with the business, organization, governance and standards.

It all sounds very much blah blah blah… In some way we are all dealing with governance, from a user- or admin perspective. Let’s kick off with a very often used feature in Power BI, but we all should have our doubts, Export to Excel.

I picked this as a first topic, based on recent experiences at a client, which resulted in interesting and challenging conversations with Power BI users across the organization. I also know that Reza Rad wrote a similar blog post earlier this year about the same topic, but I try to shine a different light on it, more from a Power BI Service admin perspective. Though, some of the content is overlapping.

Export to Excel

Export to Excel is a feature in Excel which is available in Power BI for a very long time. It allows report users to export the data from a specific visual in the report to an editable Excel file. After exporting, they can do whatever they want. For example, sending the data to others via mail, transforming or manipulating the data, start building new reports based on the Excel file and many other things. The export option can be used by clicking the ellipsis on the right top of a visual (if the visual header is enabled).

If you have all export functionalities enabled, users can both export underlying data and summarized data. The difference is mainly raw data or only data as visible in the chart where you clicked the export button.

As export, the following options are available:

• Summarized Data in Excel format (max 150k rows)
• Summarized Data in CSV format (max 30k rows)
• Underlying Data in Excel format (max 150k rows)

The export functionality can be disabled on several levels. First, Power BI Service admins can disable this functionality on tenant level. With that, nobody will be able to export the data. More about that later. Second, as a dataset owner you can decide if you allow your users to export the data. This is managed in dataset settings, but only changeable in Power BI desktop.

No matter what settings are applied in Power BI desktop, the tenant settings will overrule this. In the end the Power BI Service admin decides what options are possible to use.

Why you should consider disabling Export to Excel

In the previous part it was already shortly mentioned, Power BI Service admins can change the settings to allow data exports on tenant level. These settings are not simply on or off but can also be limited to a specific group of people.

Power BI tenant settings

The Power BI admin portal allows the following options:

• Enable for the entire organization.
• Enable for the entire organization, except specific security groups.
• Disable for the entire organization.
• Disable for the entire organization, except specific security groups.

Besides the various permissions you can set, there are also two different options to disable this functionality. First of all is the Export data in general and second the Export to Excel as a specific setting. Both have the same setup for permissions as described above.

Export Data

Export data, applies to all different capabilities to (re-)use data outside of Power BI, including Analyze in Excel and Power BI Service Live Connect features. The last mentioned also includes building new Power BI reports in Power BI desktop on top of an existing Power BI dataset. A feature which is very Powerful and much used.

Toggling off the Export Data functionality has big implications and results in a whole bunch of limitations.

Export to Excel

The toggle to disable Export to Excel only includes the functionality to Export underlying data (raw data) used to generate the visuals. This setting is not applicable to the summarized data options.

Consider disabling Export to Excel

There are multiple reasons you must take into account in order to decide if you want to disable the Export to Excel functionality. Below I highlight a few of them in more detail.

By-passes row level security
Imagine that you have RLS enabled on your dataset, but one user decides to export the data to an Excel file. Potentially this user can send it over mail or in any other possible way to others. As Power BI admin you do not have any influence on that and even worse, you don’t even know that this is happening. Possibly the data send over mail is confidential and strictly secured, but still sent to users who does not have sufficient privileges to view this data. This might lead into a possible data leak.

Unsustainable processes and new solutions
What I see happening a lot, is that Power BI is sometimes almost used as an ETL tool. People do their transformations in Power Query in Power BI, build a table in the report and export it again. Power BI is not intended for this, to my opinion at least.

It is not only people doing that for their own purpose, but also your report consumers exporting data from your report and build new (Power BI) solutions on top of their exported Excel file. Exporting is and will remain a manual task where the whole processes is depending on this one person exporting the data on regular bases, with as a result a quite unsustainable solution based on static data. If you, as a dataset owner, want to prevent this from happening, no matter what the tenant settings are, you can already disable the export functionality on dataset level as I described earlier in this blog.

Alternatives to support further analysis

Of course, you want to support your users in the organizations as much as possible to work- or become data driven. Since Excel is familiar to a lot of people, disabling this functionality can also be a killer for Power BI adoption and people stop using it. Therefor you have to give them alternatives to support your users in the most optimal way. Depending on what goal you try to achieve, one of the below options might be a good alternative to support your users.

Analyze in Excel,
Using the Analyze in Excel functionality still allows your users to work in Excel and gives the opportunity to further analyze their data, create pivot tables or even graphs, but is way different than export data. Because of the following reasons:

Analyze in excel…

• …respects row-level-security, so users will only see the data they can see based upon the security rules applied.
• …is a live connection to your Power BI dataset, so not a static dataset. By clicking the refresh button in Excel, it re-renders the data in Excel.
• …still allows people to send the Excel file over email to others. But in case they do not have sufficient privileges on the dataset, they cannot refresh the data.
• …does not have a row limit, which is 150k rows for export.

For more details about the advantages of Analyze in Excel in replacement of exporting data, check out Reza Rad his blogpost and the Microsoft documentation.

Leverage pre-build dataflows
In case you want to drive self-service data model building in Power BI and let them reuse content from your dataset, but avoid them to export data and build new solutions on top of that, dataflows might be your solution. With dataflows, you share data artifacts with others, where you have full control over what you share, but is not static or relying on manual steps. If you grant your users access to pre-build dataflows, they can easily import data from the dataflows into their own dataset and start building their own solutions.

While working with dataflows, you can think about the following advantages:

• You only load the data from the source system to Power BI once, which results in less performance impact on your source system. After that you can leverage this data within Power BI easily across multiple workspaces.
• You can grant access to others for reuse of the dataflow by granting viewer permissions on workspace level.
• Dataflows does not have any functionality like row level security. Still you can setup different views of the same dataflow by taking advantages of linked entities in dataflows (Power BI Premium license required). Setting up the security properly requires putting the linked entities in different workspaces for every target audience.

Are dataflows new for you? Check out the documentation about dataflows here.

Dataset reuse with build permissions
For further analysis, you can also allow others to leverage your dataset, so they can build new measures and new reports on top of your dataset. With this setup, they a minimum knowledge and basic understanding of Power BI should be enough, which is a huge potential for this setup. You may even think about endorsement of your datasets like giving it a promoted or certified stamp.

Recently, I have described the setup for sharing datasets via app or workspace in more detail in another blog post, that you can find here.

Wrap-up and remarks

To wrap things up, allowing users to export data from Power BI to Excel, is likely to be resulting in unsustainable manual processes and solutions build on top of static data generated and reliable on individuals that have access to this specific dataset. Besides that, take into account that this can be a possible security risk as result of by-passing row level security.

However, exporting data to Excel brings all these downsides with it as described, the alternatives take some risks with them as well. For example, it is still possible to send an Excel file based upon Analyze in Excel including the last data over mail, or people can send PBIX files over mail or any other sharing mechanism.

Some people even might think, if you still allow them to export to CSV or do Analyze in any other way outside of Power BI, are half measures, why not disable exporting to Power BI at all? Well, there is a straight forward answer to that, as you can see in above screenshot of the Power BI tenant settings. Disabling all export functionality, directly means that reuse of datasets in new Power BI desktop files is also disabled.

So, disabling Export to Excel is something that might have big consequences that need to be sorted. This may result in providing training for your end users, because they simply do not know how to work with Analyze in Excel or other alternatives, or maybe even tell them that Power BI is not intended for the goal they try to achieve.