从可视化效果导出数据Export data from visualizations

若要查看用于创建可视化效果的数据,可以在 Power BI 中显示该数据或将这些数据以 .xlsx 或.csv 文件形式导出到 Excel。If you'd like to see the data that is used to create a visualization, you can display that data in Power BI or export that data to Excel as an .xlsx or .csv file.

Watch 将从其报表中的其中一个可视化效果导出数据,将其保存为 .xlsx 文件,并在 Excel 中打开它。Watch Will export the data from one of the visualizations in his report, save it as an .xlsx file, and open it in Excel. 然后按照视频下面的分步说明来自己尝试一下。Then follow the step-by-step instructions below the video to try it out yourself.

Power BI 仪表板上的可视化效果From a visualization on a Power BI dashboard

  1. 选择可视化效果右上角的省略号。Select the ellipses in the top right corner of the visualization.

  2. 选择导出数据图标。Choose the Export data icon.

  3. 数据导出到.csv 文件中。The data is exported to a .csv file. 如果筛选了视觉对象,那么下载的数据也将进行筛选。If the visual is filtered, then the downloaded data will also be filtered.
  4. 你的浏览器将提示你保存该文件。Your browser will prompt you to save the file. 保存后,在 Excel 中打开该 .csv 文件。Once saved, open the .csv file in Excel.

从报表中的可视化效果From a visualization in a report

为此,打开“编辑视图”中的“采购分析示例报表”。To follow along, open the Procurement analysis sample report in Editing view. 添加新的空白报表页Add a new blank report page. 然后按照以下步骤来添加聚合和可视化效果级别筛选器。Then follow the steps below to add an aggregation and a visualization-level filter.

  1. 创建一个新的柱形图。Create a new column chart. 从字段窗格中,选择“位置 > 城市”和“发票 > 折扣百分比”。From the Fields pane, select Location > City and Invoice > Discount Percent. 需要将“折扣百分比”移动到“值”一列中。You may have to move Discount Percent into the Value well.

  2. 将折扣百分比的聚合从“计数”更改为“平均”。Change the aggregation for Discount Percent from Count to Average. 在“值”一列中,选择“折扣百分比”右侧的箭头(它可能显示“折扣百分比计数”),然后选择“平均”。In the Value well, select the arrow to the right of Discount Percent (it may say Count of Discount Percent), and choose Average.

  3. 向“城市”添加筛选器以删除“亚特兰大”。Add a filter to City to remove Atlanta.

    现在准备尝试使用两个选项导出数据。Now we're ready to try out both options for exporting data.

  4. 选择可视化效果右上角的省略号。Select the ellipses in the top right corner of the visualization. 选择导出数据。Choose Export data.

  5. 如果你的可视化效果具有聚合(一个示例是如果将“计数”更改为“平均”、“总和”或“最小”),则具有两个选项:汇总数据和基础数据。If your visualization has an aggregate (one example would be if you changed Count to average, sum or minimum), you'll have two options: Summarized data and Underlying data. 如需有关了解聚合的帮助,请参阅 Power BI 中的聚合For help understanding aggregates, see Aggregates in Power BI.

  6. 选择“汇总数据” > “导出”并选择 .xlsx 或 .csv。Select Summarized data > Export and choose either .xlsx or .csv. Power BI 导出数据。Power BI exports the data. 如果已将筛选器应用到可视化效果,则导出的数据将在筛选后导出。If you have applied filters to the visualization, the exported data will export as filtered. 选择“导出”时,浏览器会提示你保存文件。When you select Export, your browser prompts you to save the file. 保存后,在 Excel 中打开该文件。Once saved, open the file in Excel.

    汇总数据:如果你没有聚合或者如果有聚合但不想查看完整的细目,请选择此选项。Summarized data: select this option if you don't have an aggregate or if you do have an aggregate but don't want to see the complete breakdown. 例如,如果有一个显示 4 条的条形图,则将获得 4 行数据。For example, if you have a bar chart showing 4 bars, you will get 4 rows of data. 汇总数据可作为 .xlsx 和 .csv。Summarized data is available as .xlsx and .csv.

    在此示例中,Excel 导出会显示每个城市的总计。In this example, our Excel export shows one total for each city. 由于已筛选出亚特兰大,因此它并不包含在结果中。Since we filtered out Atlanta, it is not included in the results. 电子表格的第一行显示的是我们在从 Power BI 提取数据时所使用的筛选器。The first row of our spreadsheet shows the filters that were used when extracting the data from Power BI.

  7. 现在尝试选择“基础数据” > “导出”并选择 .xlsx。Now try selecting Underlying data > Export and choosing .xlsx. Power BI 导出数据。Power BI exports the data. 如果已将筛选器应用到可视化效果,则导出的数据将在筛选后导出。If you had applied filters to the visualization, the exported data will export as filtered. 选择“导出”时,浏览器会提示你保存文件。When you select Export, your browser prompts you to save the file. 保存后,在 Excel 中打开该文件。Once saved, open the file in Excel.

    警告

    使用导出基础数据功能,用户可以查看所有详细的数据(数据中的每列)。Exporting underlying data allows users to see all the detailed data -- every column in the data. Power BI 服务管理员可以为其组织关闭此功能。Power BI service administrators can turn this off for their organization. 如果你是数据集的所有者,则可以将专有列设置为“隐藏”,这样它们就不会出现在 Desktop 或 Power BI 服务的“字段”列表中。If you are a dataset owner, you can set proprietary columns to "hidden" so that they don't show up in the Field list in Desktop or Power BI service.

基础数据:如果你的可视化效果无聚合,并且你想要查看所有基础详细信息,请选择此选项。Underlying data: select this option if your visualization does have an aggregate and you'd like to see all the underlying details. 基本上,选择“基础数据”会删除聚合。Basically, selecting Underlying data removes the aggregate. 选择“导出”时,数据将导出到 .xlsx 文件,并且你的浏览器会提示你保存该文件。When you select Export, the data is exported to an .xlsx file and your browser prompts you to save the file. 保存后,在 Excel 中打开该文件。Once saved, open the file in Excel.

在此示例中,Excel 导出显示我们的数据集中每一个城市行的一行,以及该单个条目的折扣百分比。In this example, our Excel export shows one row for every single City row in our dataset, and the discount percent for that single entry. 换言之,数据平展而不聚合。In other words, the data is flattened and not aggregated. 电子表格的第一行显示的是我们在从 Power BI 提取数据时所使用的筛选器。The first row of our spreadsheet shows the filters that were used when extracting the data from Power BI.

限制和注意事项Limitations and considerations

  • 最多可将 30,000 行从 Power BI DesktopPower BI 服务导出到 .csv。The maximum number of rows that can be exported from Power BI Desktop and Power BI service to .csv is 30,000.
  • 可以导出到 .xlsx 的最大行数是 150,000。The maximum number of rows that can be exported to .xlsx is 150,000.
  • 如果数据源是 Analysis Services 实时连接、版本低于 2016 且模型中的表没有唯一键,则无法使用“基础数据”进行导出。Export using Underlying data will not work if the data source is an Analysis Services live connection and the version is older than 2016 and the tables in the model do not have a unique key.
  • 如果为要导出的可视化效果启用了“显示不含数据的项”选项,则无法使用“基础数据”进行导出。Export using Underlying data will not work if the Show items with no data option is enabled for the visualization being exported.
  • 使用 DirectQuery 时,最多可以导出 16MB 数据。When using DirectQuery, the maximum amount of data that can be exported is 16 MB. 这可能会导致导出的行数少于上限,尤其是当有许多列、难以压缩的数据以及其他增加文件大小但减少导出行数的因素时。This may result in exporting less than the maximum number of rows, especially if there are many columns, data that is difficult to compress, and other factors that increase file size and decrease number of rows exported.
  • Power BI 仅支持导出使用基本聚合的视觉对象。Power BI only supports export in visuals that use basic aggregates. 不支持导出使用模型或报表度量值的视觉对象。Export is not available for visuals using model or report measures.
  • 目前不支持自定义视觉对象和 R 视觉对象。Custom visuals, and R visuals, are not currently supported.
  • 不可以对使用已与其共享的仪表板的组织外用户导出数据。Export data is not available for users outside of your organization who are using a dashboard that has been shared with them.
  • 如果 .csv 文件中存在 unicode 字符,那么 Excel 中的文本可能不会正常显示。If there is unicode character in the .csv file, the text in Excel may not display properly. 不过,在记事本中打开它则会正常显示。Although, opening it in Notepad will work fine. Unicode 字符的示例是货币符号和外语单词。Examples of unicode characters are currency symbols and foreign words. 对此的解决方法是将 csv 文件导入到 Excel 中,而不是直接打开 csv 文件。The workaround for this is to import the csv into Excel, instead of opening the csv directly. 如何执行此操作:To do this:

    1. 打开 ExcelOpen Excel
    2. 从“数据”选项卡上,选择“获取外部数据” > “从文本”。From the Data tab, select Get external data > From text.
  • Power BI 管理员可以禁用数据导出功能。Power BI admins have the ability to disable the export of data.

后续步骤Next steps

Power BI 中的仪表板Dashboards in Power BI
Power BI 中的报表Reports in Power BI
Power BI - 基本概念Power BI - Basic Concepts

更多问题?More questions? 尝试咨询 Power BI 社区Try asking the Power BI Community