刷新使用 OneDrive 或 SharePoint Online 上的 Excel 工作簿创建的数据集Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

可导入存储在本地计算机或云存储(如 OneDrive for Business 或 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?

从 OneDrive 或 SharePoint Online 导入文件是确保在 Excel 上所做的工作与 Power BI 服务保持同步的好方法。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 上的文件进行更改,如添加新度量值、更改列名称,或编辑可视化效果,则保存更改后,通常在大约 1 小时内,这些更改就会被更新到 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 for Business。OneDrive or OneDrive for Business. 有什么区别?What’s the difference?

如果同时拥有个人 OneDrive 和 OneDrive for Business,建议保留要导入到 OneDrive for Business 中的 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 for Business 通常是无缝的,因为你用于登录 Power BI 的同一个帐户通常是用来登录 OneDrive for Business 的同一个帐户。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 并再次从个人 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 for Business 或 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 for Business 或 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 for Business 或 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 和通过 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