How To Easily Export to Excel with Power BI Sidetools
While working in Power BI desktop, we often need to export to Excel some data sharing with customers or colleagues. Once shared, we don’t want these users to refresh the data.
Coming with version 1.1.0, Power BI Sidetools exports your data into Excel with only one click. Then you can setup pivot tables, add theme & graphics, macros and do whatever you want. You can even generate a paginated report. Next time you will open this file from Power BI Sidetools, it will be refreshed and you will keep all the setup you did previously. Super quick and super efficient in few steps.
You may have a look at this blog post about Power BI Sidetools before : https://thebipower.fr/index.php/power-bi-sidetools/
Create an Excel file connected to the pbix file
Open Power BI Sidetools from your Power BI desktop report using the ‘External tools’ tab. Power BI Sidetools will be connected to your report.
In Power BI Sidetools, under the ‘Files’ menu, click on ‘Export to Excel/Create connected Excel file’:
Then, an Excel file will be created with a connection to the Power BI desktop model. To be precise, it is a connection to the local instance of Analysis Services created by Power BI desktop. Until Power BI desktop is closed, the connection will not need Power BI Sidetools.
You can setup the pivot table automatically created. Please notice you cannot use implicit measures in the pivot table. That means you cannot make a sum of a numeric column. You will have to create a measure calculating the sum. That’s an explicit measure and a very good practice.
Force the connection to be refreshed when the file is opened
If you want fresh data when you will open the Excel file next time, you will need to click on the refresh button in the ‘Data’ tab of the ribbon. This can be done automatically changing the settings of the connection in Excel :
- Open the ‘Data’ tab of the ribbon
- Click on ‘Connexions’ button
- Click on ‘Properties’ button
- Check ‘Refresh data when opening the file’
Format and document data in Excel sheets
You are totally free to setup the Excel file like :
- click inside the pivot table and select a theme in the ‘Creation’ tab of ‘Pivot table tools’
- click inside the pivot table and add graphics or segment in the ‘Insert’ tab
- add macros ad save as .xlsm
- add a Power View tab
- add comments
- lock sheets to prevent modifications
Eventually print or save a paginated report as a PDF
You may setup the print settings and export as a PDF. You will get a paginated report.
Eventually convert the pivot table into a flat table
- Duplicate the sheet containing the pivot table and display the new sheet
- Be sure all the data you need is displayed
- Select a cell within the pivot table
- In the ribbon, select the ‘Creation’ tab under ‘Pivot table tools’
- Remove totals and subtotals
- Click on ‘Report layout’ button, then select ‘Show in tabular form’ and ‘Repeat all item labels’
- Right click a cell containing + or – icon, then select ‘Expand/collapse’ / ‘Expand entire field’
- In the ribbon, select the ‘Analyze’ tab under ‘Pivot table tools’
- Click on ‘OLAP tools’ button, then select ‘Convert to formulas’
Share the document
Do not hesitate to share the Excel file on the network. Users will not be able to refresh data but it will still be dynamic.
Please consider the pivot table can be modified allowing users to view any data so locking modifications might be a good option.
Reuse the Excel file in the future with just one click
Once your Excel report is setup, you may open it at any time but it will content the last refreshed data.
If you want to refresh it, open the Excel report with Power BI Sidetools:
Create as many Excel reports as you need and store them in the Exports folder. They will appear right near the ‘Export to Excel’ menu item.
Best practice
As a good practice, I suggest you create a calculated table in Power BI desktop with the appropriate filters. You will just need to display this table within Excel’s pivot table.
Hiding tables and columns within Power BI desktop will also help to setup the Excel file.
References about Export to Excel
In the service, there is a possibility to “Analyze in Excel” which creates an Excel file connected to the service :
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel
Marco Russo from SQLBI developped an external tool to create an Excel file connected to the desktop. I reused a part of Marco’s code to create the connection between Power BI desktop and the Excel file. Thank you Marco !
https://www.sqlbi.com/tools/analyze-in-excel-for-power-bi-desktop/
You may want to look at the original version of this blog post : https://thebipower.fr/index.php/2020/10/30/easily-export-to-excel-with-power-bi-sidetools/
