從視覺效果匯出資料Export data from visualizations

如果您想要查看用來建立視覺效果的資料,可以在 Power BI 中顯示該資料或將資料匯出至 Excel 成為 .xlsx 或 .csv 檔案。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.

觀看 Will 從其報表的其中一個視覺效果中匯出資料、將資料儲存為 .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

  • 可從 Power BI DesktopPower BI 服務匯出至 .csv 的資料列數上限為 30,000。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 時,可匯出的最大資料量為 16 MB。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