Exporting data from a Power BI dashboard or report to Excel is not always easy. Find out how to transfer datasets between the two Microsoft applications, and how to take a training course to master Power BI completely.
Importing data from Microsoft Excel is a very common task for Power BI users. Once imported into the Business Intelligence platform, the data can be analysed to generate reports and dashboards.
However, it is sometimes necessary to export a dataset from Power BI to Excel. This can be a complex process for a beginner, but there are several very effective methods.
What is Power BI?
Power BI is Microsoft’s Business Intelligence platform, which has been named a Leader in its category in the Gartner Magic Quadrant Report for 15 consecutive years.
This tool collects, analyses and transforms data into usable information. This information can then be presented in the form of easily understandable graphs and diagrams, enabling faster decision-making within the company.
The Power BI application is available on PC, mobile and via the cloud. It can be used from anywhere in the world to harness business intelligence.
This software suite enables data to be analysed and information to be shared in the form of visualisations. It offers a host of useful features, including dataset filtration, DataViz, interactive dashboards, AI-enhanced analysis, natural language queries, connection to a wide range of sources, and data export to Excel.
💡Related articles:
Who can export Power BI datasets to Excel?
Power BI is designed as a self-service tool, accessible to all members of an organisation. However, not everyone is necessarily authorised to export data from the platform.
By default, Power BI reserves the export of the underlying dataset to administrators and report creators. If the owner of the dataset does not define specific export permissions, ordinary users cannot export data from a report or dashboard.
The dataset owner may decide to allow users to export the complete dataset, or only summarised data, or simply to prohibit all exports.
In this case, users can request data by contacting the owner. The owner’s contact details are usually given under the title of the report. Simply click on the link to open a new message from your mailbox.
The Power BI software automatically inserts a link to the report and the owner’s email address. All that remains is to fill in the additional information and send the message. The owner of the dataset can then share it as an .xlsx or .cvs file.
How do I copy a table to Power BI Desktop?
Copying a report dataset to Power BI Desktop is very easy. Simply open the report and choose the Data icon from the three icons on the left of the screen.
The dataset appears on the screen, with the Fields panel on the right. Right-click on the table and choose Copy Table.
The dataset is copied to your clipboard. All that’s left to do is open a new Excel file and paste the dataset onto the spreadsheet. You can then share this Excel file.
How do I export data from a Power BI report?
Things get more complicated if you are not using the Desktop version of Power BI. You can export data from a report, but the possibilities depend on the permissions set by its creator.
If the creator set export permissions when the report was created, you should be able to export the dataset. Move the mouse cursor over the view to be exported, and click on the ellipsis at the top right of the screen.
In the drop-down menu that opens, choose the data export option. Then choose the .xlsx or .csv format from the list. Then click on the export button, and click on ‘open’ when the web browser suggests that you do so.
Unfortunately, this export method is limited to summary data. It is therefore possible to download the data presented in the visualisation, but not the complete dataset. To obtain the full dataset, you need to contact its owner.
How do I authorise export to Power BI Desktop?
To enable exporting from Power BI Desktop, open the File menu and choose Options and Settings. In the options panel on the left, in the Current File section, choose Report Settings.
Select the appropriate export settings and confirm your choice. Users will now be able to download all the data, not just the summary version.
How do I export from a Power BI dashboard?
A Power BI dashboard is intended for end users. To export data from a dashboard, the process is similar to that for reports. However, if permissions are not enabled, no data can be exported.
Export options can be limited to a csv file. Such a file can be opened in Excel by clicking on the Data tab.
How do I export a dataset from Power BI?
The permissions set by the owner or creator of a dataset determine whether you can download it from a Power BI report or dashboard.
This task is therefore straightforward for the owner of the dataset via Power BI Desktop. On the other hand, users must contact the owner to ask them to configure export permissions or to send them the dataset.
💡Related articles:
Exporting Power BI data: what are the limits?
To prevent this from happening, report creators and Power BI administrators can set up data sharing protocols. The aim is to restrict the way in which individuals or groups can share their data.
If you are unable to obtain editing privileges for your data, or can only export certain visuals or dashboard elements, you can contact your Power BI moderator.
However, other factors may prevent a Power BI table from being exported to Excel. These may include the permissions set by the user, the type or content of the data, or the name given to the fields by the designer. Before exporting data from a Power BI visual, make sure you have the necessary permissions.
In addition, bear in mind that the export limit is set at 30,000 rows for a .csv file on Power BI Desktop and Power BI Service. Check that the table you wish to export to Excel does not exceed this limit.
Power BI applications can export a maximum of 150,000 rows to an .xlsx file. This number can be further reduced by query limits and the type of visuals.
The maximum volume that Power BI can export with DirectQuery is 16MB of uncompressed data. This may be less than the limit of 150,000 table rows if there are many columns, or if the data types are difficult to compress.
When exporting data from a Power BI matrix, count the number of data intersections per row. A Power BI table contains one data intersection per row, but matrices have more.
The option to “export data to Power BI using the underlying data” will not work if your version of Power BI is prior to 2016, if the tables in the model do not have a unique key, if the functionality has been disabled by the administrator or report creator, or if you enable the option to “show items without data” for your visualization.
Power BI custom visuals and R language visuals cannot be exported. In addition, Power BI only exports data from the first table if the visualisation contains data from many other tables and there is no active relationship for these tables in the data model.
By double-clicking on a column field and typing in a new name, you can rename the field. This is an alias, and can cause field names to be duplicated on a Power BI report. However, Excel does not allow duplication and fields will revert to their original names when exported.
You should also be aware that Unicode characters are not displayed correctly on the Excel workbook when exporting Power BI data in .csv format. You can resolve this problem by importing the data using the “Get external data from text” option in Excel.
How do I learn to use Power BI?
Power BI software is being used more and more in companies. However, just like exporting data to Excel, this tool has many subtleties.
To learn how to use the software, DataScientest is the place to be. Our Power BI training course enables beginners to acquire complete mastery in just five days.
This course is entirely distance learning via the Internet. As a Microsoft Learning Partner, DataScientest is authorised to prepare you and help you pass the PL-300 exam to obtain Power BI Data Analyst Associate certification.
For funding, our government-recognised organisation is eligible for funding options. Don’t wait any longer: find out more about the Power BI training at DataScientest!
