Graphed LogoGraphed
Login

How to Extract Data from Power BI to Excel

Cody Schneider•

You've built your report in Power BI, but now you need to get that data into Excel for some quick ad-hoc analysis, to share with a colleague, or to combine it with another data source. This article will show you several ways to extract data from Power BI to Excel, from quick copy-paste jobs to more powerful live connections.

Why Move Data from Power BI to Excel?

While Power BI is a fantastic tool for visualization and large-scale data modeling, there are plenty of valid reasons to pull your data into trusty old Excel:

  • Familiarity: Many people are simply more comfortable with Excel. Its grid layout, powerful formulas, and PivotTable features are second nature for ad-hoc analysis.

  • Collaboration: Your stakeholder might not have a Power BI license or the time to learn the interface. Sending them a static Excel file is often the easiest way to share a snapshot of the data.

  • Offline Analysis: You may need to work with a subset of your data on the go, without a live internet connection to the Power BI service.

  • Custom Calculations: While Power BI's DAX language is incredibly powerful, some users find it faster to perform one-off, complex calculations or build financial models in Excel.

Whatever your reason, knowing the right export method can save you a ton of time. Let's look at the best ways to get it done.

Method 1: Export Summarized Data from a Visual

This is the most common and straightforward way to export what you see on the screen. It takes just a few clicks and is perfect for when you need a simple summary of the data presented in a specific chart or table.

Summarized data means you're getting the aggregated values, not the individual raw rows behind them. For example, if you have a bar chart showing total sales per product category, this method will export a list of categories and their corresponding total sales figures.

Step-by-Step Instructions:

  1. Navigate to your report in the Power BI service and find the visual you want to export data from.

  2. Hover your cursor over the visual. A set of icons will appear in the top-right corner. Click the ellipsis (...) for "More options."

  3. From the dropdown menu, select "Export data."

  4. A dialog box will appear. Make sure the “Summarized data” option is selected.

  5. Under "File format," choose ".xlsx (Excel) workbook with live connection" or another desired format. Typically, the default Excel option works best.

  6. Click the "Export" button. Your file will be processed and begin downloading in your browser.

Pros:

  • Fast and easy. It’s the quickest way to get exactly what's shown in a visual into an Excel sheet.

  • Concise. It provides a clean, aggregated view perfect for high-level summaries.

Cons:

  • Limited dataset. You only get the aggregated data, not the granular details.

  • Row limits. Power BI imposes export limits. While these are generous (often 150,000 rows for .xlsx), it’s something to be aware of for extremely large summaries.

Method 2: Export Underlying Data from a Visual

What if you need more detail than just the summary? Exporting underlying data gives you the raw, row-level data that Power BI uses to create the specific visual. Using the same example of "sales by product category," this method would give you a table of every individual sales transaction that falls into those categories, not just the summarized totals.

Step-by-Step Instructions:

  1. Just like before, hover over the visual you want to extract from and click the ellipsis (...).

  2. Select "Export data" from the menu.

  3. In the pop-up window, this time select the "Underlying data" option.

  4. Choose your preferred file format (.xlsx is usually the best choice).

  5. Click "Export." After processing, the file will be downloaded.

A Note on Permissions

Don't see the "Underlying data" option? Your Power BI administrator or the report's creator may have disabled this feature for the report or for your user role. This is often done to protect sensitive data or manage server performance. If you need this data, you may have to request permission from the dataset owner.

Pros:

  • Granular detail. Gives you access to the row-level data, which is essential for deeper analysis.

  • Contextual. The exported data is still filtered by the context of the visual and any other slicers applied to the report page.

Cons:

  • Potential for large files. The underlying data can be much larger than the summarized view, leading to bigger file sizes and longer processing times.

  • Subject to permissions. This option can be disabled by administrators.

  • Row limits still apply. Like with summarized data, there is a cap on how many rows you can export this way.

Method 3: Create a Live Connection with "Analyze in Excel"

This is by far the most powerful and flexible method. Instead of a static, one-time export, "Analyze in Excel" creates a live, refreshable connection between an Excel workbook and your Power BI dataset (officially called a "semantic model").

This turns Excel into a client for your Power BI model, allowing you to build new PivotTables and PivotCharts in a familiar environment while using the managed, secure data from Power BI as the source.

Step-by-Step Instructions:

  1. Go to the workspace in the Power BI service where your dataset or report is located.

  2. Find the dataset you want to analyze. Click the ellipsis (...) next to its name and select "Analyze in Excel." (You can also get here from the ellipsis menu on the report itself.)

  3. An .odc (Office Data Connection) file will be downloaded. Open this file.

  4. Excel will launch and may ask for your permission to enable the connection. Click "Enable."

  5. A blank PivotTable will appear on your sheet. The "PivotTable Fields" pane on the right will be populated with all the tables, columns, and measures from your Power BI dataset.

  6. You can now drag and drop fields to build your own reports, pivot tables, and charts just as you would with any other data source in Excel.

Important Notes:

  • Licensing: You need a Power BI Pro or Premium Per User (PPU) license to use "Analyze in Excel."

  • Refreshing Data: To get the latest data from Power BI, go to the "Data" tab in the Excel ribbon and click "Refresh All." This pulls the most recent data from the dataset without you having to re-export anything.

  • Prerequisites: The first time you use this feature, Excel might prompt you to install specific OLE DB drivers. Follow the on-screen instructions, it's a straightforward, one-time setup.

Pros:

  • Live connection. Your Excel file can always be updated with the latest data with a single click.

  • Full data access. You're connected to the entire dataset, not just the data behind a single visual.

  • No row limits. Because it works like a PivotTable, it’s not constrained by the same row-count limitations as direct exports.

  • Secure. The connection respects any row-level security configured in the Power BI model.

Cons:

  • Requires a Power BI Pro or PPU license. Free users can't use this feature.

  • Slightly more complex setup. Requires opening the .odc file and a basic understanding of PivotTables.

Method 4: Copy a Table Visual

Need a quick, no-fuss copy/paste of a small table? This might be the method for you. It's the digital equivalent of taking a screenshot, except you can paste the actual data values.

Step-by-Step Instructions:

  1. On your Power BI report page, find or create a Table or Matrix visual.

  2. Click the ellipsis (...) on the visual.

  3. Select "Copy table." The data will be copied to your clipboard.

  4. Switch to Excel, click a cell, and paste (Ctrl+V or Cmd+V).

Pros:

  • Extremely fast for small data snippets.

  • Maintains basic formatting from the visual.

Cons:

  • Only works for table and matrix visuals.

  • Not suitable for large amounts of data.

  • Creates a completely static, disconnected copy.

Final Thoughts

Getting your data out of Power BI and into Excel is a common task, and choosing the right method depends entirely on your goal. For a quick snapshot of a summary, export from a visual. For deeper, ad-hoc analysis using a familiar tool, the "Analyze in Excel" feature is your most robust option. And for a simple transfer of a small table, a direct copy-and-paste will do the trick.

Ultimately, the manual process of exporting and importing data - whether from Power BI to Excel or across a dozen other marketing and sales platforms - highlights the need for a more direct path to answers. At Graphed, we address this by connecting all your data sources, like Google Analytics, HubSpot, Shopify, and social ad platforms, in one place. Instead of spending your time exporting data, you can simply ask questions in plain English and have dashboards, reports, and insights generated for you in real-time, completely skipping the manual work.