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.

Have a look at this blog post about Power BI Sidetools and download it in the ‘Setup’ chapter : 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

Solution 1

After you set up the pivot table like explained above :

• Display the grand total of the pivot table
• Double click on the grand total cell. A new sheet is created with a table (not a pivot table)
• Right click on any cell of the table and select ‘Table/Edit query’
• Feel free to modify the command text with any DAX formula. Ex : EVALUATE ‘Your table name‘

Solution 2

• 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’

Save the Excel file

Save the Excel file in the ‘Exports’ folder located in ‘C:\Users\your username\Documents\Power BI Sidetools\your report.pbix\5. = EXPORTS =======================’. The name of exports folder might be changed in the settings.
Next time you will go to ‘Export to Excel’ menu, you will find your Excel file.

Share the Excel file

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.

Good practices

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/

Custom analyser

With ‘Custom analyser’ feature, Power BI Sidetools users can use external tools built by the…

Read More

DAX debugger in Power BI Sidetools

DAX debugger is meant to help Power BI report developers displaying sample data from a…

Read More

Manage your external tools easily with Power BI Sidetools

Since the Power BI team allowed to launch external tools from the Power BI desktop…

Read More

Powerful text and file search in Visual Studio Code

As Power BI Sidetools creates files from your report, you are able to search through…

Read More

Compare DAX and M formulas in Visual Studio Code

Compare DAX and M formulas in Visual Studio Code is really easy with the help…

Read More