As a data analyst, you’ve created a dashboard containing tables and matrices. Now, you need to share this data with executives who prefer Excel. Let’s walk through the process of exporting your matrix table using Power BI’s “Export data” feature and then converting it to Excel format.
In this tutorial we will assume the following data model: EmployeePerformance table with the following fields: (EmployeeID: Integer, Department: Text, Year: Date, PerformanceScore: Decimal)
Save matrix data from Power BI to csv and Excel
- Create your matrix table in Power BI in the following way
- Drag ‘Department’ to Rows
- Drag ‘Year’ to Columns
- Drag ‘PerformanceScore’ to Values
- Apply any desired formatting or conditional formatting
- In Power BI Desktop, right-click on the matrix visual or click the ellipsis (…) in the top-right corner of the visual.
- Click “Export Data” and choose a location to save the CSV file.
- Next, we will convert the csv to an Excel spreadsheet file:
a. Open Excel and create a new workbook
b. Go to the “Data” tab and click “From Text/CSV”
c. Select your exported CSV file and click “Import”
d. In the “Text Import Wizard”:- Choose the appropriate delimiter (usually it is the ubiquitous comma)
- Select “Use first row as headers” – if applicable
e. Click “Load” to import the data into Excel
- In Excel, you can now reformat the data as needed to match your Power BI visual:
- Use “Pivot Table” to recreate the matrix structure if necessary
- Apply formatting and conditional formatting as desired
Troubleshooting:
- If the “Export data” option is grayed out, ensure you have the necessary permissions in Power BI Visualization pane.
- For large datasets, try filtering your data before exporting to avoid timeouts or incomplete exports.
- If the CSV import in Excel doesn’t correctly separate columns, try using Excel’s “Text to Columns” feature under the “Data” tab.
- To preserve date formatting, you may need to change the column format in Excel after importing.
- You might consider using storing the data in a Pivot Table for easy data manipulation.
Choose the right export shape
Power BI can offer different export choices depending on the visual, report permissions, tenant settings, and whether you use the service or Desktop. Summarized data exports the aggregated values represented by the visual. Data with current layout, when available, tries to preserve the rows and columns shown in a table or matrix. Underlying data exposes more detailed records only when the model and administrator allow it.
For a matrix with drill levels, expand or collapse the hierarchy to the exact level you need before exporting. Apply slicers and filters, then note them in the workbook or filename. An export is a snapshot; it does not automatically update when the report changes.
After export, compare a few totals with the Power BI visual. Matrix subtotals, blank categories, rounding, and “Show items with no data” can make the spreadsheet look different even when the export completed successfully. Do not immediately sum every exported row, because detail and subtotal rows can cause double counting.
Troubleshoot a missing export option
If Export data is absent or disabled, check whether the report owner disabled it for the visual, the Power BI administrator restricted export for the tenant, or your role lacks access to underlying data. A visual based on live connections, custom visuals, or protected data may support fewer choices. Ask for the minimum permission or approved export needed rather than trying to bypass the restriction.
For a repeatable Excel workflow, consider Analyze in Excel when your organization permits it. That creates a connected PivotTable experience instead of a one-time CSV or XLSX snapshot, but it still respects the Power BI semantic model and your permissions.
Before distributing an export:
These checks turn the export into an auditable handoff instead of an unexplained spreadsheet whose numbers cannot be reproduced.