How to export a matrix or table from Power BI to Excel?

by

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

  1. 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
  1. In Power BI Desktop, right-click on the matrix visual or click the ellipsis (…) in the top-right corner of the visual.
  1. Click “Export Data” and choose a location to save the CSV file.
  2. 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
  3. 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.