Export data from Power BI Visuals

Excel has been the backbone of finance departments for decades. Almost every company has multiple spreadsheets that are business critical. Many Power BI adoption programmes have aimed to reduce the reliance on such spreadsheets. But old habits die hard and Excel is an amazing tool and Power BI will never replace it completely. So, we have to accept that the business will want to export Power BI data into Excel. This is one option, creating pivot tables from a dataset I will cover in another post.

YouTube Version

Export data from a visual

When viewing a report in the Power BI Service, the viewer can click on the three dots usually in the top right of the visual. When the menu that appears, they can click Export data. (See Admin and Report Settings sections if its not there) Then it will display a dialog giving potentially multiple ways to export data.

Different options will be available based on the visual selected and the report options. The details for each option will be covered.

Data with current layout

This option is only available on Table or Matrix visuals. It creates an Excel file with almost exactly the same layout. The file is a pure export and does not include a refresh.

Summarized Data

Assuming the admin and report creator have allowed export, this option is available for most visuals. When selected it has three possibilities. The live connection is only available if you have contributor or higher access, i.e. its not available if you have Viewer access.

• Excel with live connection, 500,000 row max.
  Live connection means the data can be refreshed at a future time.
• Excel, 150,000 row max
  Export without refresh.
• CSV file, 30,000 row max
  CSV cannot include a refresh.

Underlying Data

This option is only available if the report creator has enabled it. (See Report Setting Options below) It exposes more columns than summarized. In this example it exports all the columns from the fact table, the 2 columns from the dimension table used and the measure used.

Applied Filters in Export Data

When the data is exported, the exported data will be filtered with the report filters applied. Then the filter details are included in Excel files. CSV files will be filtered but will not include the details.

Report Setting Options

A report creator can control if report readers can export data from the report. These settings are found under File – Options, CURRENT REPORT – Report settings. Then look under Export data. The first option is the default and will allow Data with Current Layout and Summarized data. When option 2 is selected, underlying data export is enabled as well. Option 3 disables all exports from this report.

Admin Options

Power Admins have ultimate control over this feature. There are 3 features they can restrict, enable or disable. These options are Export to Excel, Export to csv and Users can work with Power BI datasets. Disabling the third option will disable files having a live refresh.

Also by using security groups, you can restrict any of these features to limited users across the tenancy.

Data Export Conclusion

Educating users in good practice on using these features will help your strong Excel users feel more comfortable. I can understand companies restricting parts of this, but I would hope it is not just a blanket ban hoping this will speed up adoption of Power BI. In my experience it will just make some people more stubborn. Stubborn workarounds will be shadow IT and increase your technical debt.

Resources

The UK Weather pbix file is on my github site – https://github.com/Laura-GB/DemoData#video-and-blog-resources