How to Copy Data from Power BI to Excel

Moving data from a Power BI report into an Excel spreadsheet is a common task, but it's not always as straightforward as it seems. Whether you need to run a quick ad-hoc analysis, share a slice of data with a colleague, or integrate it into an existing Excel model, this guide will walk you through the best methods to get the job done. We'll cover everything from simple copy-pasting to direct connections, showing you how to export exactly the data you need.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Copy Data from Power BI to Excel?

Power BI is a phenomenal tool for interactive data visualization and business intelligence, but sometimes you just need the raw numbers in a good old-fashioned spreadsheet. Here are a few common reasons why you might find yourself needing to export data:

• Ad-Hoc Analysis: You might want to experiment with the data in ways not anticipated in the Power BI report, like trying out new formulas or creating a quick pivot table.
• Sharing with Others: Not everyone on your team or in your organization may have a Power BI license or the know-how to use it. Exporting to Excel makes the data accessible to all.
• Integration with Existing Workflows: Many business processes still live in Excel. Exporting data allows you to integrate insights from Power BI into these established financial models, forecasts, or operational spreadsheets.
• Data Archiving: You may need to take a static snapshot of a report at a specific point in time, like month-end or quarter-end, for record-keeping.

Whatever your reason, knowing the right way to export can save you a lot of time and frustration. Let's look at the primary methods to accomplish this.

Method 1: Direct Copy and Paste from Visuals

The simplest method is a direct copy and paste, which works best with table or matrix visuals. It's quick, easy, and great for grabbing small, specific data sets.

How to Copy and Paste:

1. In your Power BI report (either in Power BI Desktop or the Power BI Service), find the table or matrix visual containing the data you want.
2. Hover over the visual to bring up the 'More options' ellipsis icon (…). Click on it.
3. From the dropdown menu, select 'Copy visual as table'. This copies the data to your clipboard.
4. If you want to copy just a small selection, you can also often right-click on a value within the table and choose 'Copy' -> 'Copy value' or 'Copy selection'.
5. Open your Excel spreadsheet, click on the cell where you want the data to start, and paste (Ctrl+V or Command+V).

When to Use This Method:

This approach is perfect when you need a very small, specific chunk of data right away. It's the digital equivalent of jotting down a few numbers from a report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Limitations:

While convenient, this method is very limited. The formatting can sometimes be inconsistent, and it doesn't work well for large amounts of data. It only copies the summarized data as it's displayed in the visual, so you can't get to the underlying rows that make up the aggregate values.

Method 2: Export Data from a Visual

The most common and flexible way to get data out of Power BI is by using the built-in export function on a visual. This gives you more control over the format and volume of data you receive.

How to Export Data:

1. Navigate to the visual you want to export data from.
2. Hover over the visual and click the 'More options' ellipsis icon (...).
3. Select 'Export data'. A dialog box will appear.
4. Here you will see a few options for how to export. Let's break down what they mean.

Understanding Your Export Options

The choices you see in the export dialog are very important, as they determine what data you get and in what format. You'll typically see these three options:

1. Data with current layout

This option exports the data exactly as it appears in the visual, respecting the current design, grouping, subtotals, and layout. It creates an .xlsx (Excel) file.

• Best For: Replicating the visual's table structure in Excel. If you have a nicely organized matrix with subtotals and you want to keep that structure, this is your choice.
• Limit: Up to 150,000 rows when exporting from the Power BI Service.

2. Summarized data

This option exports the summarized data from the visual. It strips a lot of the formatting and layout to provide a cleaner data table suitable for further analysis. You can choose either an .xlsx or .csv format.

• Best For: Getting the aggregated values behind the chart to build your own pivot table or chart in Excel.
• Limit: For .xlsx, the limit is also 150,000 rows. For .csv, Power BI Pro users can export up to 30,000 rows.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Underlying data

This is the most powerful option because it allows you to export the detailed data that the visual is built upon. Instead of just the aggregated numbers (like total sales per category), you can get every individual row of data that feeds into that aggregation (like every single sales transaction).

• Best For: Deep-dive analysis when you need the most granular level of detail available in the report.
• Important Note: This option will only be available if the report creator has enabled it. If the option is grayed out, it's due to the report's permissions.
• Limit: For Premium users, you can export up to 150,000 rows to a .csv file. This is significantly more than other options, making it suitable for larger datasets. The limit for Power BI Pro users is also 30,000 rows.

Keep in mind, your organization's Power BI admin may have adjusted or disabled these exporting limits. If you run into issues, it's worth checking with them.

Method 3: Analyze in Excel

For a more dynamic and interactive way to work with your Power BI data in Excel, the "Analyze in Excel" feature is fantastic. Instead of exporting a static CSV or Excel file, this method creates a live pivot table in Excel that is directly connected to the Power BI dataset online.

This means your data in Excel can be refreshed to pull the latest information from the dataset, giving you an always-up-to-date look at your numbers without ever leaving the spreadsheet.

How to Use Analyze in Excel:

1. Go to the report or dataset in the Power BI Service (app.powerbi.com).
2. Click the 'Export' menu in the top bar.
3. Select 'Analyze in Excel'. (Note: you might also find this under the 'More options' ellipsis on a report or dataset listing).
4. This will download an .ODC (Office Data Connection) file. Open this file on your computer.
5. Excel will prompt you for security permissions. Click 'Enable'.
6. Voila! You will now see an empty pivot table in Excel, and on the right side of your screen will be the PivotTable Fields list. This list contains all the tables, measures, and columns from your Power BI dataset. You can now drag and drop these fields to build pivot tables and charts just as you would with any other data source in Excel.

When to Use This Method:

This is the best method if you consider yourself a power user of Excel pivot tables and want to explore the data model slicing and dicing in a familiar environment. It's also excellent if you need to build reports in Excel that must be refreshed periodically.

Prerequisites:

To use this feature, you'll need a Power BI Pro or Premium Per User (PPU) license. You also need to have "build" permissions for the dataset you're connecting to. Finally, you may need to install an Excel library update if you're using an older version of Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 4: The Performance Analyzer "Hack"

If you're looking for a slightly more technical "power user" trick, you can use the Performance Analyzer in Power BI Desktop to grab a visual's data. This tool is designed to monitor and analyze report performance, but a useful side effect is that it shows you the exact DAX query used to generate a visual.

How to Use the Performance Analyzer:

1. Open your report in Power BI Desktop.
2. Go to the 'View' tab and check the box for 'Performance analyzer'. A new pane will open on the right.
3. Click 'Start recording' in the Performance analyzer pane.
4. Now, refresh your visuals or change filters to trigger them to run. The action will be logged in the pane.
5. Each visual will appear as an item in the list. Expand the visual you're interested in, and you will see a 'Copy query' option.
6. Click 'Copy query'. This copies the underlying DAX code to your clipboard.
7. You can then run this query using an external tool like DAX Studio to see the exact data table the visual receives. While less direct, this method gives you precise insight into what moves a chart and is invaluable for debugging complex visuals.

Final Thoughts

Getting your data from the interactive environment of Power BI into the versatile cells of Excel is a fundamental skill for any data professional. We've covered quick copy-pastes, detailed data exports, live connections with Analyze in Excel, and even a "hack" using the Performance Analyzer. The best method depends entirely on what you're trying to achieve - from a quick check to building a complex, refreshable model.

While these methods are powerful, they often involve manual processes and the challenge of juggling multiple tools. At Graphed, we've found that the best analytics happen when you can move directly from question to insight without getting stuck on manual exports. That's why we created a platform where you can connect all your data sources - from marketing platforms to sales CRMs - and use simple natural language to build real-time dashboards and reports. Instead of exporting data to answer follow-up questions, we allow you to chat with your data to get answers in seconds, taking you from raw numbers to actionable insights faster than ever.