Most clients we work with use cloud-based Power BI service for their data consumption. Or, if exporting data to the cloud is a no-go, they use the on-prem Power BI Report Server. Being purpose-built by Microsoft for this use case, these two platforms offer robust direct data consumption functionality.
Recently, a client planned to use only Power BI Desktop and wondered about the data export options available to them. Power BI Desktop is primarily a development tool, so it has limited functionality as a consumption platform. There are community tools that provide limited workarounds to extend that functionality.
If you find yourself wondering what data and visualization export options exist using only Power BI Desktop, here are 5 methods – listed in order of descending feasibility – that you can consider.
1. File > Export > Export to PDF
Using this command will generate a PDF of all unhidden pages. For more information, see the documentation.
- Using this mechanism has limitations over which you have no control:
- Hidden pages are excluded.
- All unhidden pages are included. Therefore, the render can take a while.
- The PDF will automatically save to the Downloads folder.
- Any visual with horizontal or vertical scrollbars will not show the entirety of their data contents.
- This export mechanism is merely a glorified collection of screenshots.
2. Export to CSV
To use Export to CSV, use the “…” menu on any visual. Unlike Power BI service, where you can export summarized data to Excel, this feature is much more limited in Power BI Desktop. Learn more. (Click the Power BI Desktop and Power BI service tabs in the Export data from a report section to learn more.)
- The CSV row limit is pretty low: 30,000 rows.
- The XLSX row limit (again: not available in Power BI Desktop) is higher, but still not huge: 150,000 rows.
- Workaround to consider: define a set of “export-friendly” table visuals on one or more new pages that are purely for the purpose of exporting to CSV and sending to clients.
3. Analyze in Excel
This method is an extremely popular one for “skipping” the visualization layer and more directly accessing the data in the underlying semantic model. While this functionality is only available in the Power BI service (Learn more), there are a couple community tools that somewhat facilitate it locally. We have used these both extensively for development, and they are fantastic, but we have never had end users leverage these for data consumption, as they can be confusing.
- Analyze in Excel for Power BI Desktop. This method has some considerable limitations; most important, while you can save the Excel file, you cannot refresh it.
- Excel Workbook Connection. Although a finicky way to go, this method does allow the user to refresh the Excel file against the local Power BI Desktop instance.
4. DAX Studio or Power BI Exporter
Both of these tools can be used to extract the data out of the underlying data model tables from Power BI Desktop.
- Get more info on DAX Studio. Get more info on Power BI Exporter.
5. Paginated reports
This term is Power BI parlance for classic printable multi-page reports. (You may recall an older Microsoft product called Reporting Services; that was rebranded as Power BI paginated reports a few years back.)
Paginated reports are developed using an entirely different tool Power BI Report Builder.
- Report Builder will let you build an easily exported multi-page PDF (that’s really its main purpose). But, if you are only using Power BI Desktop, it will likely not be feasible for a couple of reasons:
- The dataset must reside in the Power BI service or Power BI Report Server.
- It requires Power BI Premium, which has license implications (learn more).
To learn more about creating paginated reports using Report Builder, view our free on-demand webinar.
So many data distribution options, so many Power BI licenses
Each Power BI license type allows for different ways to export data and distribute reports. Puzzling out the best license to meet your requirements is complex. To clear up the confusion, we created a matrix to help match up the type of Power BI license with your data distribution requirements.
There’s lots to know – and lots you can accomplish – with Power BI. If you have any questions about this blog, licensing or anything related to Power BI and integrating it into your BI environment, get in contact with us. No matter where you are in your Microsoft and Power BI implementation, you can trust us for knowledgeable support.
