Have you ever had problems with exporting Power BI data to Excel? A recent issue a client ran into was in trying to export a very wide Power BI crosstab to Excel and being able to see that data in Excel with the entire crosstab visible, as well as the ability to print it. Additionally, they wanted to be able to distribute it, ideally in an Excel format.
To do this, the options available in Power BI are PowerPoint, PDF or print. Unfortunately, these options only give you a snapshot of what is on the screen. This is a very limited option as we can’t drag over to the right and see what’s not visible on the screen.
Here’s a simple way to solve this dilemma:
- In Power BI, under the More Options menu, we use the option called Analyze in Excel.
- When you click that it gives you an ODC connection file. When you open that it gives you an Excel workbook.
- That is where the magic happens. This workbook is connected to the model that’s in Power BI, similar to an Excel pivot being connected to a Tabular cube.
- Using a pivot table, we were able to create that workbook and save it to our local instance.
- We saved a copy of this file to a network location (in our case we used the SharePoint site).
- In SharePoint we can upload that file and when we open that document, it will connect and be imported into the network shared location.
- We can then click on the ellipsis next to the document and click copy link which will give you a hyperlink to copy.
- Next, go back into the Power BI desktop and in the Home menu select text box. In that box use the link option and paste in that hyperlink.
- Then publish it to Power BI. Now when in the Power BI crosstab workspace, there is a link at the bottom called Excel version.
- That link brings us to a clean version of this crosstab data. Anytime you hit data refresh all, it will bring up the latest version of the data set.
- Another way to share this without sharing the direct link is by sharing the location (in my case SharePoint) and a user can copy the link and get the latest version of the file.
- Another thing to note is this works with row level security that is built into the Power BI model if there is any, and it would filter the data to however the security is set up.
This simple solution allowed us to export the data that was in the crosstab with a link at the bottom to the Excel file for the customer/user to click on that link, open the Excel file and print it. They can also copy and paste it and distribute the data as needed without having to worry about the export options.
If you have any questions about exporting Power BI data to Excel or Power BI in general, we can help. Does your business use Power BI but you're maxing out your bandwidth dealing with Power BI security, management, compliance and end user support? Our Power BI Managed Services centralize the management and architectural support, removing the burden, so you can focus on growing your business. Click below to learn more.