Graphed LogoGraphed
Login

How to Extract Data from Power BI Dashboard

Cody Schneider•

While Power BI is fantastic for creating interactive and insightful dashboards, sometimes you need to get the raw numbers out to use them elsewhere. You might need to perform a custom analysis in Excel, drop a summary table into a PowerPoint presentation, or share a static snapshot with a team member. This guide will walk you through the simplest and most effective ways to extract data directly from your Power BI dashboards and reports.

Why Extract Data from Power BI in the First Place?

Power BI is designed to be a final destination for data visualization, so the idea of pulling data back out might seem backward. However, there are plenty of practical reasons to do it:

  • Deeper Analysis in Spreadsheets: Excel and Google Sheets are unbeatable for ad-hoc analysis. You may want to run your own calculations, create pivot tables from a specific subset of data, or use Excel formulas that aren't available in Power BI.

  • Sharing with Non-Power BI Users: Not everyone on your team or in your client's organization will have a Power BI license. Exporting data to a CSV or Excel file is a quick way to share key information with them.

  • Creating Presentations and Documents: You often need to include summary tables or charts in Google Slides, PowerPoint, or Word documents. A simple data export is much easier than taking screenshots.

  • Data Archiving: You might need to save a snapshot of your data at a specific point in time - like for an end-of-quarter report - before the underlying dataset is refreshed.

Whatever your reason, the goal is the same: to get a clean, usable copy of the data a visual is displaying. Let's look at the best ways to accomplish this.

Method 1: Exporting Summarized Data from a Visual

This is the most straightforward and common method for extracting data. It gives you the exact data that is being displayed in a specific chart or table - nothing more, nothing less.

Think of it this way: if you have a bar chart showing total sales by month, exporting summarized data will give you a spreadsheet with two columns: "Month" and "Total Sales." You won't get the individual transactions, just the aggregated totals used to build the chart.

Step-by-Step Guide:

  1. Navigate to the report page in Power BI that contains the visual you want to extract data from.

  2. Hover your cursor over the visual. A set of icons will appear in the top-right corner.

  3. Click on the ellipsis icon (…) to open the "More options" menu.

  4. From the dropdown menu, select "Export data." A new dialog box will appear.

  5. Under the "Data" option, you will see a radio button for "Summarized data." This is usually selected by default.

  6. Change the "File format" if you need to. You can choose between an Excel file (.xlsx) which keeps the formatting, or a CSV file (.csv) which is plain text and more universally compatible.

  7. Click the "Export" button. The file will then be downloaded to your computer.

Things to Keep in Mind:

  • Row Limits: When exporting to .xlsx, there's a limit of 150,000 rows. For .csv files, the limit is typically 30,000 rows. If your visual contains more data than that, it will be truncated.

  • What You See Is What You Get: Any filters applied to the visual or the report page will also apply to your export. This is actually a feature, as it lets you easily narrow down your data before exporting.

Method 2: Exporting Underlying Data from a Visual

What if the summarized data isn't enough? Suppose your bar chart shows total sales per category, but you need to see every single transaction that contributes to those totals. This is where exporting underlying data comes in handy.

This option gives you the raw, detailed data from the dataset that powers the visual. It will include all the relevant columns from the source table, not just the ones visible in the chart.

Step-by-Step Guide:

  1. Just like before, hover over the visual you're interested in and click the ellipsis (…).

  2. Select "Export data."

  3. In the dialog that appears, switch the selection from "Summarized data" to "Underlying data."

  4. Once you select this, you'll see a notification about how many rows you'll be exporting. You can choose between an Excel file (.xlsx) or a CSV file (.csv). Excel is usually better if your data contains different data types you want to preserve.

  5. Click "Export," and your file containing the detailed data rows will be downloaded.

Going back to our previous example, if your bar chart shows total sales per category, exporting the underlying data would give you a spreadsheet with columns like Transaction ID, Date, Product Name, Quantity, Price, and Category for every single sale.

Permissions and Limits for Underlying Data:

This is a more powerful export option, so it comes with a few more restrictions that are often set by Power BI administrators:

  • Admin Controls: A company's Power BI admin can disable the ability to export underlying data for the entire organization for security or compliance reasons.

  • Report-Level settings: The creator of the report can also turn this option off in the report's settings. If you don't see the "Underlying data" option, it is likely been disabled by one of these settings.

  • Row Limits: The row limits for underlying data are usually higher. For users with a Power BI Pro license, the limit is often 150,000 rows to Excel. For users on a Premium capacity, this can go up significantly.

Method 3: Using 'Analyze in Excel' for a Live Connection

Exporting data is great for static snapshots, but what if you want to explore the entire dataset in Excel and have it stay connected to Power BI? The "Analyze in Excel" feature is built for exactly this.

Instead of a one-time data dump, this method creates a live pivot table in Excel that is connected directly to your Power BI dataset. You can slice, dice, and pivot the data however you want, and you can even refresh it to pull the latest information from Power BI.

Step-by-Step Guide:

  1. Navigate to the report or the dataset itself within the Power BI Service.

  2. In the menu bar at the top, look for the "Export" menu, and from there select "Analyze in Excel." In some views, the "Analyze in Excel" button might be visible on its own.

  3. This will download a small file with an .odc (Office Data Connection) extension. This isn't the data itself, it's a file that tells Excel how to connect to your Power BI dataset.

  4. Open the downloaded .odc file with Excel. You may be prompted to enable editing or enable an external data connection - you must allow this for it to work.

  5. Voila! A new Excel workbook will open with a blank PivotTable. On the right, you'll see a list of all the tables and fields from your Power BI dataset, ready for you to drag and drop.

Why this method is so powerful:

  • Full Flexibility: You're not limited to the data in a single visual. You get access to the entire model to build your own reports in Excel.

  • Data Stays Fresh: Since it's a live connection, you can go to the "Data" tab in Excel and hit "Refresh All" to pull the most recent data from your Power BI dataset without having to re-export anything.

  • Great for Excel Power Users: If you're more comfortable building pivot tables than navigating a Power BI report, this is your best friend.

Common Issues You Might Encounter

If you're having trouble getting your data out, it's usually due to one of these reasons.

1. The "Export data" option is missing or greyed out

This is almost always a permissions issue. The creator of the dashboard or a Power BI administrator has likely restricted data exporting on this report. You’ll need to contact the report owner and ask for permission.

2. Limitations of Custom Visuals

Not all of the custom visuals available in Power BI support data exporting. If you're trying to export from a non-standard chart, you may find the option is unavailable.

3. You're hitting row limits

If your exported file seems incomplete, you have probably exceeded the row limit. Try applying more filters in the Power BI report to reduce the amount of data you're exporting before you try again. For very large datasets, using "Analyze in Excel" is often the best alternative.

Final Thoughts

Power BI is an incredible tool for visualizing and interacting with your data, but its utility doesn't have to stop there. Knowing how to extract both summarized and underlying data unlocks a new level of flexibility, allowing you to seamlessly integrate your business intelligence insights with other tools like Excel for deeper, custom analysis.

While pulling data from Power BI addresses specific analytical tasks, we know the real challenge is often consolidating data in the first place. That’s why we built Graphed. Our platform connects directly to all your favorite marketing and sales tools and lets you use simple, natural language to instantly build the dashboards and get the answers you need. It turns hours of manual reporting into a thirty-second plain-English conversation, so you spend less time wrestling with complex BI tools and more time acting on insights.