Formatted Export to Excel / Spreadsheets in Power BI

When you export data in a Power BI table or matrix report to Excel, you quickly run into a few limitations. Let us take this sample report.

Shown below is the data from the above Power BI matrix exported to an excel file. You would notice the following:

• Number formatting & scaling: The spreadsheet does not retain number formatting and scaling.
• Cell formatting (e.g., background color) is lost.
• The expand/collapse functionality in rows & columns, if any, are lost.
• The rows collapsed in the matrix are not exported (e.g., child rows of US-East region)

Finally, data from a table & matrix can be exported to only one worksheet. It is not possible to create a workbook with multiple worksheets.

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 similar Inforiver Matrix report. This report consists of two pages, with Page 1 for International and Page 2 for US geography. We have used a smart page break based on the region – which when used will automatically push the next region to a new page.

Data Exported to Excel From Inforiver Matrix

Shown below is the same data exported to Excel. You will instantly notice that the data from the two pages has been exported to two worksheets/tabs in Excel. In addition, you will also notice that Inforiver successfully exports the following:

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

Note that you can also opt to receive these Excel exports in your email using Inforiver scheduler.

Export to Excel – A Quick Summary

Overall, Inforiver provides the following capabilities while exporting to Excel.

1. Supports all navigation modes (hierarchy, table, stepped and drill down).

2. Retains status of row/column hierarchies in your Power BI matrix style reports when you export to Excel. i.e., If some branches are collapsed, there is the option to perform the export as you see (WYSIWYG export) - or export all the records fully expanded. Note that there are some restrictions for column hierarchies with 3 or more category levels.

3. Allows export of 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 7 hierarchy levels. It saves you a ton of time as you do not have to recreate these row hierarchies in Excel.

4. Retains font styles, background, and alignment.

5. Preserves number formatting and scaling.

6. Supports visual-level advanced and nested (and/or) filters applied to the table.

7. Supports inline notes, column notes & comments.

8. Exports calculated columns, measures, and rows.

9. Exports all rows & columns – and not just the records that are visible in the page/screen.

10. While using a page break, your export will create an Excel file with multiple worksheets in Excel (Paginated export to excel).

11. If you have the enterprise edition, you can also schedule Excel files to users in your organization. Users can receive the Excel file in 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.

Visit our documentation page to learn more about Export to Excel.