Power Bi Desktop Export Matrix To Excel With Formatting
Olegario Benford
I have seen several posts about this but I don't think there has been a solution. Essentially, I have a Matrix that my user wants to download as Excel/CSV but retain its formatting. Thus, the user does not want to play around with Power Pivot not does the user want the summarized or detail data because the formatting is lost. This is something really simple out of Tableau but for Power BI it has been a giant mountain to overcome.
For the moment, it is not possible to download and print the matrix visual in a custom format directly. Only the data used in matrix visual can be exported to a csv file. The best thing to try is to use the "Analysis in excel" feature and then view and interact with them using PivotTables, charts, slicers, and other Excel features.
Yeah, none of these options really work. The client is refusing to user Power Pivot (which seems to be the best solution). Basically, they want what was available in Tableau (just download and print). There is an option to 'Show As Table' which is what the client wants but I need to download this as a CSV:
To see the data being used to create a visual, you can display that data in Power BI, or export it to Excel. This article shows you how to export to Excel. Data can be exported to Excel from a Power BI dashboard tile and from a report visual.
Not all data can be viewed or exported by all users. There are safeguards that report designers and administrators use when building dashboards and reports. Some data is restricted, hidden, or confidential, and cannot be seen or exported without special permissions. If you're a designer or admin, select the tab for Admin and designer controls for exporting.
If you don't have permissions to the data, you can't export or open in Excel. Often, data is confidential or limited to specific users. For details, see the Considerations and limitations section at the end of this document. If you're working in the Power BI service, you can contact your Power BI administrator, or you can look up the contact information for the dashboard owner to request export permissions. To find the owner, select the dropdown next to the report title.
Report owners can classify and label reports using sensitivity labels from Microsoft Purview Information Protection. If the sensitivity label has protection settings, Power BI applies these protection settings when exporting report data to Excel, PowerPoint, or PDF files. Only authorized users are able to open protected files.
Security and Power BI administrators can use Microsoft Defender for Cloud Apps to monitor user access and activity, perform real-time risk analysis, and set label-specific controls. For example, organizations can use Microsoft Defender for Cloud Apps to configure a policy that prevents users from downloading sensitive data from Power BI to unmanaged devices.
If the tile was pinned from a report with a sensitivity label, you see this warning. Consider the sensitivity of your content before deciding whether to export or not. To export data with a sensitivity label, open the report page that contains the original visual and export the data from there instead.
Power BI exports the data to a .csv file. If you filtered the visualization, then the .csv export is filtered as well. Your browser prompts you to save or open the file. By default, your export is saved to your local Downloads folder.
Select the option for Summarized data if you want to export data for what you see in that visual. This type of export shows you only the data (columns and measures) that is being used to create the visual. Since this visual has a hierarchy, your export contains aggregated data for the full hierarchy. So, even though your current view of the chart shows two columns (two Country/Regions), your summarized data display four rows--one for each City in the hierarchy.
In Power BI Desktop, you only have the option to export summarized data as a .csv file.When you select Export, your browser prompts you to save the file. Once saved, open the file in Excel. If you're using the Power BI app in Microsoft Teams, you may not receive the same prompts. Your exported file is saved in your local Downloads folder or in a folder that you specify.
In this example, our Excel export shows one total for each city. Since we filtered out Atlanta, it isn't included in the results. The first row of our spreadsheet shows the filters that Power BI used when extracting the data.
All the data used by the hierarchy is exported, not simply the data used for the current drill level for the visual. For example, we haven't yet drilled down to the city level, but our export includes city data as well as country/region data.
Select this option if you want to see the data in the visual and other data from the semantic model (see chart later in this article for details). If your visualization has an aggregate, selecting Underlying data removes the aggregate. In this example, the Excel export shows one row for every single City row in our semantic model and the discount percent for that single entry. Power BI flattens the data it doesn't aggregate it.
For some data, underlying is disabled. Build permissions may be required to see more data than is currently displayed in the visual (underlying data). These permissions protect the data from being inappropriately viewed, reused, or shared with anyone other than the intended audience.
When you select Export, your browser prompts you to save the file. Once saved, open the file in Excel. If you're using the Power BI app in Microsoft Teams, you may not receive the same prompts. Your exported file is saved in your local Downloads folder or in a folder that you specify.
All the data used by the hierarchy is exported, not simply the data used for the current drill level for the visual. For example, we haven't yet drilled down to the city level, but our export includes both city and country/region data.
Since we applied filters to the visual, the exported data exports as filtered. Notice that the first row displays Applied filters: City isn't Atlanta, GA. Notice that in some specific cases the first row might display a filter that isn't being applied in the data that follows. See the next section for details.
What you see when you select Underlying data can vary. Understanding these details may require the help of your admin or IT department. In Power BI Desktop or the Power BI service, in the reporting view, a measure shows in the Fields list with a calculator icon. . Measures can be created in Power BI Desktop.
There are many considerations related to exporting to Excel. Exporting is one of those features that report designers and Power BI administrators may disable for individuals or even for an entire organization. They disable it to ensure that private data isn't exposed to the wrong audience.
If you find that you can't use this feature, reach out to the report owner and your administrator. They can explain why you can't export data from a particular visual or from all visuals. It may be that this feature is purposely disabled and perhaps they can enable it for you. Other times, there may be particular reasons an export doesn't work. It could be related to permissions, data contents, data type, visual type, how the designer named the fields, and more. When contacting the report owner or administrator, refer them to these articles:Admin tenant settings, Row level security, and Data protection.
For export from matrix visuals using Data with current layout, the export limit is 150,000 data intersections. For a table visual, each row has one data intersection. For a matrix visual, each row can have 1 or more data intersections, so the exported rows count can be less than 150,000. (For example, if a matrix visual has three data intersections per row, the maximum row count is 150,000 / 3 = 50,000 rows.) The message "Exported data exceeded the allowed volume. Some data may have been omitted." is added at the footer of the Excel file when the limit is hit. To avoid this scenario, consider limiting the dimensions or filtering the data.
If the Row subtotals toggle is set to 'Off' in Power BI Desktop for a matrix visual, but the matrix visual has expanded and collapsed sections, exported data contains subtotals for rows. To work around this issue, use the Expand All command from the visual's context menu.
When you're using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows of 150,000. This result can happen if:
The granularity of data exported to Excel using the Data with current layout and Summarized data options are identical and the only difference between the options is the shape of the data in Excel. For example, in a matrix visual, the Data with current layout preserves the shape of the visual as shown in Power BI when data is exported to Excel while the Summarized data option exports the same data but as a flat table of rows and columns.
When you export datetime data from Power BI, the format of the datetime changes to match the datetime format of your local machine. For example, let's say that the report designer formatted the datetime as DDMMYYYY, which is the default regional format for their locale. When you open that report on your machine and export the data to Excel, the datetime shows as MMDDYY, which is your default regional datetime format.
d3342ee215