重新整理建立自 OneDrive 或 SharePoint Online 上 Excel 活頁簿的資料集Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

您可以將儲存於本機電腦,或商務用 OneDrive 與 SharePoint Online 這類雲端儲存體的 Excel 活頁簿加以匯入。You can import Excel workbooks that are stored on your local machine, or in cloud storage such as OneDrive for Business or SharePoint Online. 我們將討論使用雲端儲存體存放 excel 檔案的優點。We will look at the advantages of using cloud storage for your excel files. 如需如何將 Excel 檔案匯入 Power BI 的詳細資訊,請參閱從 Excel 活頁簿檔案取得資料For more information on how to import Excel files into Power BI, see Get data from Excel workbook files.

有哪些優點?What are the advantages?

為了讓您在 Excel 中所做的工作與 Power BI 服務保持同步,最好的方法就是從 OneDrive 或 SharePoint Online 匯入檔案。Importing files from OneDrive, or SharePoint Online, is a great way to make sure the work you’re doing in Excel stays in-sync with the Power BI service. 已載入檔案模型的任何資料都將匯入至該資料集,而且任何您已在該檔案中建立的報表都將載入至 Power BI 中的報表。Any data you’ve loaded into your file’s model is imported into the dataset and any reports you’ve created in the file are loaded into Reports in Power BI. 如果您變更放在 OneDrive 或 SharePoint Online 上的檔案,例如加入新的量值、變更資料行名稱,或編輯視覺效果,一旦儲存之後,這些變更通常會在大約一小時內在 Power BI 中更新。If you make changes to your file on OneDrive, or SharePoint Online, like add new measures, change column names, or edit visualizations, once you save, those changes will be updated in Power BI too, usually within about an hour.

當您從個人 OneDrive 匯入 Excel 活頁簿時,活頁簿中的任何資料,像是工作表中的資料表和 (或) 載入到 Excel 資料模型的資料,以及此資料模型的結構都將匯入 Power BI 中的新資料集。When you import an Excel workbook from your personal OneDrive, any data in the workbook, like tables in worksheets and/or data that is loaded into the Excel data model and the structure of the data model, are imported into a new dataset in Power BI. 任何 Power View 視覺效果都會在報表中重新建立。Any Power View visualizations are re-created in Reports. Power BI 會自動連結 OneDrive 或 SharePoint Online 上的活頁簿,大約每個小時檢查更新。Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. 如果活頁簿已變更,Power BI 會重新整理 Power BI 服務中的資料集和報表。If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

您可以重新整理 Power BI 服務中的資料集。You can refresh on the dataset in the Power BI service. 當您手動重新整理或排程重新整理資料集時,Power BI 會直接連接到外部資料來源,以查詢更新的資料,然後載入資料集。When you manually refresh, or schedule refresh, on the dataset, Power BI connects directly to the external data sources to query for updated data it then loads into the dataset. 重新整理 Power BI 中的資料集,並不會重新整理 OneDrive 或 SharePoint Online 上活頁簿中的資料。Refreshing a dataset from within Power BI does not refresh the data in the workbook on OneDrive, or SharePoint Online.

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

在 Power BI 中,從 Power BI Desktop 檔案建立的資料集支援 [立即重新整理] 和 [排程重新整理],這些檔案是從本機磁碟匯入,本機磁碟使用 [取得資料]/查詢編輯器連接下列任何資料來源並載入資料︰In Power BI, Refresh Now and Schedule Refresh is supported for datasets created from Power BI Desktop files imported from a local drive where Get Data/Query Editor is used to connect to and load data from any of the following data sources:

Power BI Gateway - PersonalPower BI Gateway - Personal

  • Power BI Desktop 的 [取得資料] 和查詢編輯器都會顯示所有的線上資料來源。All online data sources shown in Power BI Desktop’s Get Data and Query Editor.
  • Power BI Desktop 的 [取得資料] 和查詢編輯器都會顯示所有的內部部署資料來源,但 Hadoop 檔案 (HDFS) 與 Microsoft Exchange 除外。All on-premises data sources shown in Power BI Desktop’s Get Data and Query Editor 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

注意

必須安裝並執行閘道器,才能讓 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.

OneDrive 或商務用 OneDrive。OneDrive or OneDrive for Business. 有何不同?What’s the difference?

如果您有個人 OneDrive 和商務用 OneDrive,建議您保留任何您想要匯入至商務用 OneDrive 中 Power BI 的檔案。If you have both a personal OneDrive and OneDrive for Business, it’s recommended you keep any files you want to import into Power BI in OneDrive for Business. 原因如下:您可能使用兩個不同的帳戶來登入它們。Here’s why: You likely use two different accounts to sign into them.

因為您用來登入 Power BI 的帳戶通常與用來登入商務用 OneDrive 的帳戶相同,所以通常可以無縫連接到 Power BI 中的商務用 OneDrive。Connecting to OneDrive for Business in Power BI is typically seamless because the same account you use to sign into Power BI with is often the same account used to sign into OneDrive for Business. 但如果使用個人 OneDrive,您很可能要以不同的 Microsoft 帳戶登入。But, with personal OneDrive, you likely sign in with a different Microsoft account.

當您使用 Microsoft 帳戶登入時,請確定選取 [讓我保持登入]。When you sign in with your Microsoft account, be sure to select Keep me signed in. Power BI 之後可將您在 Power BI Desktop 檔案中進行的任何更新與在 Power BI 中的資料集同步處理Power BI can then synchronize any updates you make in the file in Power BI Desktop with datasets in Power BI

如果您變更無法與 Power BI 中資料集或報表同步處理之 OneDrive 上的檔案 (因為您的 Microsoft 帳戶認證可能已更改),您將必須連接到您的檔案,並從您的個人 OneDrive 將它再上傳一次。If you make changes to your file on OneDrive that cannot be synchronized with the dataset or reports in Power BI, because your Microsoft account credentials might have changed, you’ll need to connect to and import your file again from your personal OneDrive.

連接至 Excel 檔案的選項Options for connecting to Excel file

當您連接至商務用 OneDrive 或 SharePoint Online 上的 Excel 活頁簿時,可使用兩個選項來取得 Power BI 活頁簿中的內容。When you connect to an Excel workbook in OneDrive for Business, or SharePoint Online, you’ll have two options on how to get what’s in your workbook into Power BI.

將 Excel 資料匯入至 Power BI – 當您從商務用 OneDrive 或 SharePoint Online 匯入 Excel 活頁簿時,會依上述方式運作。Import Excel data into Power BI – When you import an Excel workbook from your OneDrive for Business, or SharePoint Online, it works as described above.

在 Power BI 中連接、管理及檢視 Excel – 使用此選項時,會建立連接,以直接從 Power BI 連接到商務用 OneDrive 或 SharePoint Online 上的活頁簿。Connect, Manage, and View Excel in Power BI – When using this option, you create a connection from Power BI right to your workbook on OneDrive for Business, or SharePoint Online.

當您以這種方式連接到 Excel 活頁簿時,在 Power BI 中將不會建立資料集。When you connect to an Excel workbook this way, a dataset is not created in Power BI. 不過,活頁簿將會出現在 Power BI 服務中的 [報表] 下方,其名稱旁有 Excel 圖示。However, the workbook will appear in the Power BI service under Reports with an Excel icon next to the name. 不同於 Excel Online,當您從 Power BI 連接到您的活頁簿時,如果您的活頁簿具有與外部資料來源的連接,且該連接會將資料載入 Excel 資料模型中,您就可以設定重新整理排程。Unlike with Excel Online, when you connect to your workbook from Power BI, if your workbook has connections to external data sources that load data into the Excel data model, you can setup a refresh schedule.

當您以這種方式設定重新整理排程時,唯一的差異在於,重新整理資料都會送至 OneDrive 或 SharePoint Online 中的活頁簿資料模型,而不是 Power BI 中的資料集。When you setup a refresh schedule this way, the only difference is refreshed data goes into the workbook’s data model on OneDrive, or SharePoint Online, rather than a dataset in Power BI.

如何確定資料已載入 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.

重要注意事項Important notes

*不支援重新整理連接至 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

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