How to Export Microsoft Power BI Data to Excel
Your email has been sent
Learn how to export Microsoft Power BI data to Excel using this step-by-step guide with examples.
For the Microsoft Power BI report designer, importing Microsoft Excel data is a common task. Once in Power BI, you analyze and report on that data. Occasionally, you might need to export a Power BI dataset to Excel. Perhaps a colleague wants to further analyze the data, or you want to analyze it a bit more; the process of exporting to Excel can be easy or impossible depending on your status.
In this tutorial, I’ll show you several ways to export Microsoft Power BI datasets to the .xlsx Excel format. We’ll start with the easiest method and work through the more complex methods. The discussion will include instructions for the dataset’s owner and the end users, because the export experience will be different.
SEE: Explore TechRepublic Premium’s Microsoft Power BI Hiring Kit.
I’m using Microsoft Power BI via Microsoft Fabric on a Windows 11 64-bit system. You don’t need Microsoft Fabric; however, if you don’t have it, you might notice subtle differences in the interface.
Jump to:
Power BI is an organizational tool, so you might think that anyone with a link to a dashboard can export the underlying dataset. But that won’t always be the case. By default, Power BI restricts exporting the underlying dataset to administrators and report designers. Unless the owner of the dataset applies specific permissions for exporting, viewers — also known as consumers and end users — can’t export data via a dashboard or report.
SEE: Here’s everything you need to know about using Microsoft Power BI with this bundle from TechRepublic Academy.
Report designers, who are the owners of the dataset, control how end users can export data by setting one of these permissions:
As an end user, if you can’t export the data, you can request the data by viewing the contact information under the report title (Figure A). To do so, click the LakeHouse data hub option, click the dataset file to open its details, and then copy the owner’s email address in the Location column. If you’re lucky, the owner has the data in an Excel .xlsx or .cvs file and can email or share it quickly. If you’re not using Fabric, look for the owner by clicking the report title in the report’s title bar.
Figure A
Designers can do themselves and consumers a favor by setting a report’s permissions to allow consumers to export the underlying data when they create a report, unless there are security reasons for not doing so.
As the designer, you might want to allow exports. If so, you can set permissions in Power BI Desktop.
In design mode, click the File menu and choose Options and settings. In the resulting window, select Options. In the left pane, in the Current File section, select Report settings. To the right, select the appropriate export settings, and click OK (Figure B). As you can see, this report allows end users to download summarized data.
Figure B
As a consumer, the quickest and easiest way to export the dataset to Excel is to use the Analyze in Excel feature. While viewing the report, click Export and choose Analyze in Excel from the dropdown (Figure C).
Figure C
This feature copies the dataset to Excel for the web for quick and easy access. From there, you can download the .xlsx file to your local system. That’s the good news. The bad news is the inherent structure of the exported data: This feature exports the summarized data, literally, as a PivotTable. You don’t get the raw dataset.
SEE: Explore these Excel tips everyone should master.
Depending on your organization’s configuration, Analyze in Excel may not even be available. If you’re after the data as a simple dataset rather than a PivotTable or this feature isn’t available, you can rely on other options.
If you own the report file and a consumer asks you for its dataset, but you don’t have the original .xlsx file, you can create one using Power BI Desktop. Simply open the report in Power BI Desktop. To the left are three icons. Click Data — the one in the middle — to see the dataset.
SEE: Learn how to create a date table in Power BI.
Right-click the table and choose Copy Table (Figure D). Doing so copies the dataset to the Clipboard. From there, open a blank Excel workbook and paste the dataset into a sheet. You can now send it off to your colleague.
Figure D
As a consumer, you’ll have to work a bit harder and export from a report; what you’ll get depends on the specific permissions set by the designer or Power BI’s default settings.
Figure E shows two visualizations based on a report. That report, in turn, is based on a dataset — it’s the dataset you’re after.
Figure E
If the owner set export permissions in Power BI Desktop when creating the report, you should be able to export the dataset. The process is simple, but keep in mind that it might not return the full dataset. To export as a consumer, do the following:
Figure F
The open file will be an Excel .xlsx file. It’s unlikely that the summarized data will be what you’re after at this point. Check Step 4. If you filtered the visualization before exporting, Power BI will export the results of that filter. Return to the report, remove any filter and try again.
If you’re lucky enough to grab all the data, you can use Excel to work with this data as you would any other data.
A Power BI dashboard is the final product that the designer wants end users to see. Exporting from a dashboard is similar to the above process for reports, but unless permissions allow for exporting, you won’t get anything.
The ellipsis dropdown will have more options, and the list might limit you to exporting only as a .csv file. If you’re able to download a .csv file, you can open this file in Excel by clicking the Data tab. In the Get & Transform Data group, click Get Data. Then, choose From File and finally From Text/CSV.
Whether you can download the entire dataset from a report or dashboard depends on your relationship to the data. If you’re the owner and you have Power BI Desktop, your export task is simple. If you are a consumer and the designer didn’t set export permissions for the dataset, you must contact the designer to get the dataset.
If you’re struggling with Microsoft Power BI, explore our list of the best business intelligence tools to see which software may be a better fit for your business.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to Export Microsoft Power BI Data to Excel
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply