刷新使用本地驱动器上的 Excel 工作簿创建的数据集Refresh a dataset created from an Excel workbook on a local drive

支持的功能有哪些?What’s supported?

在 Power BI 中,数据集是使用本地驱动器导入的 Excel 工作簿创建的,它支持“立即刷新”和“计划刷新”功能。在本地驱动器中,Power Query(在 Excel 2016 中获取和转换数据)可用于连接到以下任一数据源并将数据加载到 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 和通过 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