將 Excel 活頁簿匯入 Power BI DesktopImport Excel workbooks into Power BI Desktop

透過 Power BI Desktop,您可以輕鬆地將內含 Power Query 查詢、Power Pivot 模型和 Power View 工作表的 Excel 活頁簿,匯入 Power BI Desktop。With Power BI Desktop, you can easily import Excel workbooks that contain Power Query queries, Power Pivot models and Power View worksheets into Power BI Desktop. 系統會根據 Excel 活頁簿自動建立報表和視覺效果,一旦匯入,您便可以使用現有功能及隨 Power BI Desktop 每月更新所發行的新功能,持續改進及精簡這些報表。Reports and visualizations are automatically created based on the Excel workbook, and once imported, you can continue to improve and refine those reports using Power BI Desktop, using the existing features and new features released with each Power BI Desktop monthly update.

我們未來計劃提供 Excel 與 Power BI Desktop 之間的其他通訊 (例如匯入/匯出);目前將活頁簿匯入 Power BI Desktop 的這項功能可讓現有的 Excel 使用者開始使用 Power BI Desktop。In the future we plan to provide additional communication between Excel and Power BI Desktop (such as import/export); this current ability to import workbooks into Power BI Desktop lets existing Excel users get started with Power BI Desktop.

如何匯入 Excel 活頁簿?How do I import an Excel workbook?

若要匯入活頁簿,請從 Power BI Desktop 選取 檔案>>[匯入] >Excel 活頁簿內容.To import a workbook, from Power BI Desktop select File -> Import -> Excel Workbook Contents.

隨即會出現一個視窗,讓您選取要匯入的活頁簿。A window appears, letting you select the workbook to import. 活頁簿中的物件大小或數目目前沒有限制,但針對較大的活頁簿,Power BI Desktop 需要較長的時間進行分析及匯入。There is currently no limitation on the size or number of objects in the workbook, but larger workbooks take longer for Power BI Desktop to analyze and import.

注意

若要從 [共用商務用 OneDrive] 資料夾或從 [Office 365 群組] 資料夾載入或匯入 Excel 檔案,請使用 Excel 檔案的 URL,將檔案匯入 Power BI Desktop 中的 Web 資料來源。To load or import Excel files from shared OneDrive for Business folders or from Office 365 group folders, use the URL of the Excel file, and input it into the Web data source in Power BI Desktop. 您需要遵循幾個步驟,才能使用正確的商務用 OneDrive URL 格式,因此請查閱 Power BI Desktop 中的商務用 OneDrive 連結,了解詳細資訊以及正確的步驟順序。There are a few steps you need to follow to properly format the OneDrive for Business URL, so check out Use OneDrive for Business links in Power BI Desktop for more information and the correct series of steps.

一旦選取活頁簿,Power BI Desktop 即會分析活頁簿,並將其轉換成 Power BI Desktop 檔案 (.pbix)。Once a workbook is selected, Power BI Desktop analyzes the workbook and converts it into a Power BI Desktop file (.pbix). 請注意,這是一次性事件;一旦使用上述步驟建立 Power BI Desktop 檔案,Power BI Desktop 檔案與原始 Excel 活頁簿不會有相依關係,因此您可以進行修改或變更 (以及儲存和共用),而不會影響原始活頁簿。Note that this is a one-time event; once the Power BI Desktop file is created with these steps, the Power BI Desktop file has no dependence on the original Excel workbook, and can be modified or changed (and saved, and shared) without affecting the original workbook.

匯入完成之後,會顯示 [摘要] 頁面,除了描述已轉換的項目之外,也會列出無法匯入的任何項目。Once the import is finished, a Summary page is displayed that describes the items that were converted, and also lists any items that were not able to be imported.

當您選取 [關閉] 時,報表會載入 Power BI Desktop。When you select Close, the report is loaded in Power BI Desktop. 下圖顯示匯入 Excel 活頁簿之後的 Power BI Desktop:Power BI Desktop 會根據活頁簿內容自動載入報表。The following image shows Power BI Desktop after an Excel workbook was imported: Power BI Desktop automatically loaded the report based on the workbook contents.

匯入活頁簿之後,您可以使用 Power BI Desktop 中包含的任何特性和功能繼續處理報表,例如建立新的視覺效果、加入資料或建立新的報表頁面。Now that the workbook is imported, you can continue working on the report – such as creating new visualizations, adding data, or creating new report pages – using any of the features and capabilities included in Power BI Desktop.

可匯入哪些活頁簿項目?Which workbook elements are imported?

Power BI Desktop 可匯入 Excel 中的下列項目,通常稱為「物件」 。Power BI Desktop can import the following elements, commonly referred to as objects, in Excel.

Excel 活頁簿中的物件Object in Excel Workbook Power BI Desktop 檔案中的最終結果Final Result in Power BI Desktop file
Power Query 查詢Power Query queries 所有來自 Excel 的 Power Query 查詢都會轉換成 Power BI Desktop 中的查詢。All Power Query queries from Excel are converted to queries in Power BI Desktop. 如果 Excel 活頁簿中已定義查詢群組,則會在 Power BI Desktop 中複寫相同的組織。If there were Query Groups defined in the Excel Workbook, the same organization will be replicated in Power BI Desktop. 除非在 Excel 中設定為 [只建立連線],否則會載入所有查詢。All queries are loaded unless they were set to “Only Create Connection” in Excel. 您可以在 Power BI Desktop 中,透過 [查詢編輯器] 的 [常用] 索引標籤中的 [屬性] 對話方塊來自訂載入行為。The Load behavior can be customized from the Properties dialog in the Home tab of Query Editor in Power BI Desktop.
Power Pivot 外部資料連接Power Pivot External Data Connections 所有 Power Pivot 外部資料連接都會轉換成 Power BI Desktop 中的查詢。All Power Pivot External Data Connections will be converted to queries in Power BI Desktop.
連結的資料表或目前的活頁簿資料表Linked Tables or Current Workbook tables 如果 Excel 中有工作表資料表連結到資料模型,或連結到查詢 (透過 [從資料表] 或 M 中的 Excel.CurrentWorkbook() 函數),下列選項會顯示 1。If there is a worksheet table in Excel linked to the Data Model, or linked to a query (by using From Table or the Excel.CurrentWorkbook() function in M), the following options are presented 1. 將資料表匯入 Power BI Desktop 檔案。Import the table to the Power BI Desktop file. 這是資料的一次快照,之後您便無法在 Power BI Desktop 中編輯資料表中的資料。This is a one-time snapshot of the data, after which you cannot edit the data in the table in Power BI Desktop. 使用這個選項建立的資料表有一百萬個字元的大小限制 (結合所有資料行標頭和資料格的總計)。There is a size limitation of 1 million characters (total, combining all column headers and cells) for tables created using this option. 2.2. 保留原始活頁簿的連接。Keep a connection to the original workbook. 或者,您可以保留原始 Excel 活頁簿的連接,Power BI Desktop 會在每次重新整理時擷取這個資料表中的最新內容,就像是針對 Power BI Desktop 中的 Excel 活頁簿建立的其他任何查詢一樣。Alternatively, you can keep a connection to the original Excel Workbook and Power BI Desktop retrieves the latest content in this table with each refresh, just like any other query created against an Excel workbook in Power BI Desktop.
資料模型的導出資料行、量值、資料類別和關聯性Data Model Calculated Columns, Measures, Data Categories and Relationships 這些資料模型物件會轉換成 Power BI Desktop 中的對等物件。These Data Model objects are converted to the equivalent objects in Power BI Desktop. 請注意,Power BI Desktop 目前不提供某些資料類別,例如 [映像] 。Note that there are certain Data Categories that are not available in Power BI Desktop yet, such as Image. 在這些情況下,會重設有問題之資料行的資料類別資訊。In these cases, the Data Category information will be reset for the columns in question.
Power View 工作表Power View Worksheets 您可以針對 Excel 中的每個 Power View 工作表建立新的報表頁面。A new report page is created for each Power View worksheet in Excel. 這些報表頁面的名稱和順序會符合原始 Excel 活頁簿。The name and order of these report pages match the original Excel workbook.

匯入活頁簿有任何限制嗎?Are there any limitations to importing a workbook?

將活頁簿匯入 Power BI Desktop 有些限制,包括:There are a few limitations to importing a workbook into Power BI Desktop, which are the following:

  1. Analysis Services 表格式模型的外部連接: 在 Excel 2013 中,您可以建立 SQL Server Analysis Services 表格式模型的連接,並在這些模型之上建立 Power View 報表,而不需要匯入資料。External Connections to Analysis Services Tabular Models: In Excel 2013, it is possible to create a connection to SQL Server Analysis Services Tabular models and create Power View reports on top of these models without the need to import the data. 目前不支援使用這種連接類型將 Excel 活頁簿匯入 Power BI Desktop,但未來更新將會提供。This type of connection is currently not supported as part of importing Excel Workbooks into Power BI Desktop, but will be available in an upcoming update. 在此同時,您必須在 Power BI Desktop 中重新建立這些外部連接。In the meantime, you must recreate these external connections in Power BI Desktop.
  2. KPI: Power BI Desktop 目前不支援這種資料模型物件類型。KPIs: This type of Data Model object is currently not supported in Power BI Desktop. 因此,將 Excel 活頁簿匯入 Power BI Desktop 時會略過 KPI。As such, KPIs are skipped as part of importing an Excel workbook into Power BI Desktop.
  3. 階層: Power BI Desktop 目前不支援這種資料模型物件類型。Hierarchies: This type of Data Model object is currently not supported in Power BI Desktop. 因此,將 Excel 活頁簿匯入 Power BI Desktop 時會略過階層。As such, hierarchies are skipped as part of importing an Excel Workbook into Power BI Desktop.
  4. 二進位資料行: Power BI Desktop 目前不支援這種資料模型資料行類型。Binary data columns: This type of Data Model column is currently not supported in Power BI Desktop. 二進位資料行已從 Power BI Desktop 的結果資料表中移除。Binary Data columns are removed from the resulting table in Power BI Desktop.
  5. 不支援的 Power View 項目: Power BI Desktop 目前不提供 Power View 中的一些功能,例如佈景主題或特定視覺效果類型 (具有播放軸的散佈圖、向下鑽研行為等)。Unsupported Power View elements: There are a few features in Power View that are not yet available in Power BI Desktop, such as Themes or certain types of visualizations (Scatter Chart with Play Axis, Drill-Down behaviors, etc.). 這些不支援的視覺效果會導致在 Power BI Desktop 報表中的對應位置出現「視覺效果不受支援」 的訊息,您可以視需要予以刪除或重新設定。These unsupported visualizations result in Unsupported Visualization messages on their corresponding locations in the Power BI Desktop report, which you can delete or reconfigure as needed.
  6. 使用 Power Query 之從資料表或使用 M 之Excel.CurrentWorkbook的具名範圍:目前不支援將這個具名範圍資料匯入 Power BI Desktop,但 Power BI Desktop 已規劃此更新。Named Ranges using From Table in Power Query, or using Excel.CurrentWorkbook in M: Importing this named range data into Power BI Desktop is not currently supported, but it is a planned update for Power BI Desktop. 目前,這些具名範圍會當做外部 Excel 活頁簿的連接,載入 Power BI Desktop。Currently, these named ranges are loaded into Power BI Desktop as a connection to the external Excel workbook.
  7. PowerPivot 至 SSRS: 由於 Power BI Desktop 目前不提供該資料來源,因此目前不支援 SQL Server Reporting Services (SSRS) 的 PowerPivot 外部連接。PowerPivot to SSRS: PowerPivot external connections to SQL Server Reporting Services (SSRS) are not currently supported, since that data source is not currently available in Power BI Desktop.