Formatted Export to Excel / Spreadsheets in Power BI

When exporting data in a Power BI table or matrix report to Excel, you quickly encounter a few limitations. 

To illustrate this, let us take this sample report built using the matrix visual available in Power BI.

You get the following when you export the matrix to an Excel file. 

You will notice the following shortcomings:

• Number formatting & scaling: The spreadsheet does not retain the number formatting and scaling used in Power BI.
• Cell formatting (e.g., background color) is lost.
• The expand/collapse functionality in rows & columns, if any, is lost. 
• The rows collapsed in the matrix are not exported (e.g., child rows of the US-East region). This is, in essence, a what-you-see-is-what-you-get (WYSIWYG) export.
• Data from a table & matrix can be exported to only one worksheet. It is not possible to create a workbook with multiple worksheets, say, one for each country.

Let us compare & contrast this with how you can generate formatted, multi-worksheet data exports using Inforiver for Power BI.

Formatted, Multi-Worksheet Data Exports using Inforiver Matrix

Let us take a paginated Inforiver Matrix report using a similar format. This report has two pages, with Page 1 for the International region and Page 2 for the US region. (We have used a smart page break based on the region – which, when used, automatically pushes each new region to a new page)

Data Exported to Excel from Inforiver Matrix in Power BI

When you export the contents of the Inforiver matrix to Excel, you get the following.

You will instantly notice that the data from the two pages have been exported to two worksheets/tabs in Excel. In addition, you will also notice that Inforiver successfully exports data while preserving the following:

• Cell/background formatting
• Number & decimal formatting
• Number scaling (millions etc)
• Expand-collapse rows & columns 
• Tilted column headers (at 45-degrees)
• Exports cell-level comments and notes (Jan forecast for International-APAC-Water)
• … and more

Export to Excel – Highlights

Inforiver for Power BI provides the following capabilities while exporting to Excel.

1. Preserve a variety of table and measure layouts

Table layouts (hierarchy, table, stepped, and drill down) and measure layouts (measures in rows and columns) are preserved in the exported file. The following export shows a stepped layout with measures in rows.

2. Retain the number formatting and scaling

Notice the scaling in the column headers, the decimal places, and the dollar prefix in the exported file.

3. Export rows to Excel along with the expand/collapse icons

This is very helpful when you have a lot of records & hierarchies, say 1000 records with seven hierarchy levels. It saves you much time as you do not have to recreate these row hierarchies in Excel.

4. Export with the status of row/column hierarchies

While exporting to Excel, if some branches are collapsed, you can export hidden rows or export only rows revealed to the user. In the example below, we have exercised the option of not exporting hidden rows. As a result, child rows are shown only for United States-East.

5. Export while preserving font styles, background, and alignment

The example below shows a monthly sales tracker with different background colors for past, present, and future periods. Also, notice the vertical measure headers.

6. Export with conditional formatting

The image below shows data bars, color scales for the background, ABC classification, and semantic formatting.

7. Export with visual-level filters

The advanced and nested (and/or) filters applied to the table are retained when exporting as a Excel file.

8. Export the table/matrix with notes and comments

The inline notes and notes & comments columns are retained in the Excel file, as shown below.

9. Export data input and calculated columns, measures, and rows 

Manual data entry and calculated columns, measures, and rows created by the user in the visual can also be exported. The following image shows the three inserted columns in the exported Excel file - a contribution column (in a single click using quick formulas) and two manual input columns for capturing status and date.

10. Export with custom header

The report header, including charts, KPI cards, and images, can be exported. Note that the header is exported as a single image.

11. Export visual row and column hierarchies

The row and column hierarchies created using the ‘Group’ feature are retained in the exported Excel file. The following image shows the expand/collapse icons for a newly created hierarchy inside the visual – Kitchen Appliances. Similarly, the column groups – Revenue, Profitability, and Customer are also retained along with the formatting.

12. Export all rows & columns

Export all rows and columns, not just the visible records on the page/screen. The categories TV and Video, Cameras and camcorders, and Cell phones, on the second page of the Inforiver report, are shown in the exported Excel.

13. Export the table to multiple worksheets

Export a single Inforiver table/matrix to multiple tabs in Excel using row or column breaks (Paginated export to Excel). In the below image, each category is shown in a separate tab.

14. Enable end-users to customize the export​

The end users in the reading view can customize the export using the options shown in the image below.

15. Send periodic emails with Excel attachments

If you have Inforiver Enterprise, you can also schedule Excel files for users in your organization. Users can receive the Excel file in an email based on schedule frequency - e.g., daily, Tuesdays & Thursdays, monthly, etc.

Here is a brief video outlining formatted Export to excel using Inforiver in Power BI.

To summarize, Inforiver delivers strong capabilities that help you export your Power BI reports to Excel while preserving formatting. Try the Export to Excel capability for yourself today.