重新整理建立自本機磁碟上 Excel 活頁簿的資料集Refresh a dataset created from an Excel workbook on a local drive

支援的項目有哪些?What’s supported?

在 Power BI 中,從 Excel 活頁簿建立的資料集支援 [立即重新整理] 和 [排程重新整理],這些檔案是從本機磁碟匯入,本機磁碟使用 Power Query (Excel 2016 中的 [取得和轉換資料]) 或 Power Pivot 連接下列任何資料來源並將資料載入 Excel 資料模型︰In Power BI, Refresh Now and Schedule Refresh is supported for datasets created from Excel workbooks imported from a local drive where Power Query (Get & Transform data in Excel 2016) or Power Pivot is used to connect to any of the following data sources and load data into the Excel data model:

Power BI Gateway - PersonalPower BI Gateway - Personal

  • Power Query 中顯示的所有線上資料來源。All online data sources shown in Power Query.
  • Power Query 中顯示的所有內部部署資料來源,但 Hadoop 檔案 (HDFS) 與 Microsoft Exchange 除外。All on-premises data sources shown in Power Query except for Hadoop file (HDFS) and Microsoft Exchange.
  • Power Pivot 中顯示的所有線上資料來源。*All online data sources shown in Power Pivot.*
  • Power Pivot 中顯示的所有內部部署資料來源,但 Hadoop 檔案 (HDFS) 與 Microsoft Exchange 除外。All on-premises data sources shown in Power Pivot except for Hadoop file (HDFS) and Microsoft Exchange.

內部部署資料閘道On-premises data gateway

資料來源Data source
Analysis Services 表格式Analysis Services Tabular
Analysis Services 多維度Analysis Services Multidimensional
SQL ServerSQL Server
SAP HANASAP HANA
OracleOracle
TeradataTeradata
檔案File
資料夾Folder
SharePoint 清單 (內部部署)SharePoint list (on-premises)
WebWeb
ODataOData
IBM DB2IBM DB2
MySQLMySQL
SybaseSybase
SAP BWSAP BW
IBM Informix 資料庫IBM Informix Database
ODBCODBC

注意:Notes:

  • 必須安裝並執行閘道器,才能讓 Power BI 連接至內部部署資料來源並重新整理資料集。A gateway must be installed and running in order for Power BI to connect to on-premises data sources and refresh the dataset.
  • 使用 Excel 2013 時,請確定您已更新為最新版本的 Power Query。When using Excel 2013, make sure you’ve updated Power Query to the latest version.
  • 不支援重新整理從本機磁碟機匯入的 Excel 活頁簿 (其中資料只存在於工作表或連結的資料表)。Refresh is not supported for Excel workbooks imported from a local drive where data exists only in worksheets or linked tables. 如果活頁簿資料已儲存並從 OneDrive 匯入,就可支援重新整理。Refresh is supported for worksheet data if it is stored and imported from OneDrive. 如需深入了解,請參閱重新整理建立自 OneDrive 或 SharePoint Online 上 Excel 活頁簿的資料集To learn more, see Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online.
  • 當您重新整理從本機磁碟機匯入之 Excel 活頁簿中建立的資料集時,只會重新整理來自資料來源的查詢資料。When you refresh a dataset created from an Excel workbook imported from a local drive, only the data queried from data sources is refreshed. 如果您變更 Excel 或 Power Pivot 中的資料模型結構;例如,建立新的量值或變更資料行的名稱,則這些變更將不會複製到資料集。If you change the structure of the data model in Excel or Power Pivot; for example, create a new measure or change the name of a column, those changes will not be copied to the dataset. 如果您進行這類變更,您就必須重新上傳或重新發行活頁簿。If you make such changes, you’ll need to re-upload or re-publish the workbook. 如果您預期對活頁簿結構進行一般變更,而且您想該變更反映在 Power BI 中的資料集,而不必重新上傳,請考慮將您的活頁簿放在 OneDrive 上。If you expect to make regular changes to the structure of your workbook and you want those to be reflected in the dataset in Power BI without having to re-upload, consider putting your workbook on OneDrive. Power BI 會自動重新整理儲存在 OneDrive 並從中匯入的活頁簿之結構和工作表資料。Power BI automatically refreshes both the structure and worksheet data from workbooks stored and imported from OneDrive.

如何確定資料已載入 Excel 資料模型?How do I make sure data is loaded to the Excel data model?

當您使用 Power Query (Excel 2016 中的 [取得和轉換資料]) 來連接到資料來源時,您有幾個載入資料位置的選項。When you use Power Query (Get & Transform data in Excel 2016) to connect to a data source, you have several options where to load the data. 若要確定您將資料載入資料模型,您就必須在 [載入] 對話方塊中選取 [新增這項資料到資料模型] 選項。To make sure you load data into the data model, you must select the Add this data to the Data Model option in the Load To dialog box.

注意

這裡的圖片顯示的是 Excel 2016。The images here show Excel 2016.

在 [導覽] 中,按一下 [載入至...]。In Navigator, click Load To…

或者,如果您在 [導覽] 中按一下 [編輯] ,您就會開啟 [查詢編輯器]。Or, If you click Edit in Navigator, you’ll open the Query Editor. 其中您可以按一下 [關閉並載入至...]。There you can click Close & Load To….

接著請確定在 [載入至] 中已選取 [將此資料加入資料模型] 。Then in Load To, make sure you select Add this data to the Data Model.

如果在 Power Pivot 中使用 [取得外部資料]?What if I use Get External Data in Power Pivot?

沒問題。No problem. 每當您使用 Power Pivot 連接和查詢來自內部部署或線上資料來源的資料時,資料就會自動載入至資料模型。Whenever you use Power Pivot to connect to and query data from an on-premises or online data source, the data is automatically loaded to the data model.

我要如何排程重新整理?How do I schedule refresh?

當您設定重新整理排程時,Power BI 會直接連接到資料來源,方法是使用資料集內的連接資訊和認證來查詢更新資料,然後將更新資料載入資料集。When you setup a refresh schedule, Power BI will connect directly to the data sources using connection information and credentials in the dataset to query for updated data, then load the updated data into the dataset. 此外,也會更新任何在 Power BI 服務中該資料集上的報表和儀表板視覺效果。Any visualizations in reports and dashboards based on that dataset in the Power BI service are also updated.

如需如何設定排程重新整理的詳細資訊,請參閱設定排程重新整理For details on how to setup schedule refresh, see Configure Schedule Refresh.

發生錯誤時When things go wrong

如果發生錯誤,問題通常起因於 Power BI 無法登入資料來源,或資料集連接至內部部署資料來源時,閘道器離線。When things go wrong, it’s usually because Power BI can’t sign into data sources, or if the dataset connects to an on-premises data source, the gateway is offline. 請確認 Power BI 可以登入資料來源。Make sure Power BI can sign into data sources. 如果您用以登入資料來源的密碼已變更,或 Power BI 從資料來源登出,請務必嘗試在 [資料來源認證] 中再次登入您的資料來源。If a password you use to sign into a data source changes, or Power BI gets signed out from a data source, be sure to try signing into your data sources again in Data Source Credentials.

請務必核取 [傳送重新整理失敗通知電子郵件給我] 。Be sure to leave the Send refresh failure notification email to me checked. 您會想要立刻知道排定的重新整理是否失敗。You’ll want to know right away if a scheduled refresh fails.

重要

不支援重新整理連接至 Power Pivot 並從中查詢的 OData 摘要。Refresh is not supported for OData feeds connected to and queried from Power Pivot. 當使用 OData 摘要作為資料來源時,請使用 Power Query。When using an OData feed as a data source, use Power Query.

疑難排解Troubleshooting

有時候重新整理資料可能不會如預期執行。Sometimes refreshing data may not go as expected. 這通常是閘道連線問題之故。Typically this will be an issue connected with a gateway. 請參閱閘道疑難排解文章,以取得工具和已知的問題。Take a look at the gateway troubleshooting articles for tools and known issues.

為內部部署資料閘道進行疑難排解Troubleshooting the On-premises data gateway

對 Power BI Gateway - Personal 進行疑難排解Troubleshooting the Power BI Gateway - Personal

後續步驟Next steps

有其他問題嗎?More questions? 試試 Power BI 社群Try the Power BI Community