通过

Excel

  • 项目

总结

项目 说明
发布状态 正式版
产品 Excel
Power BI（语义模型）
Power BI（数据流）
Fabric（数据流 Gen2）
Power Apps（数据流）
Dynamics 365 Customer Insights
Analysis Services
支持的身份验证类型 匿名（联机）
基本（联机）
组织帐户（联机）
函数参考文档 Excel.Workbook
Excel.CurrentWorkbook

注意

由于部署计划和特定于主机的功能，某些功能可能只存在于一个产品中。

先决条件

若要连接到旧工作簿（如 .xls 或 .xlsb），需要 Access 数据库引擎 OLEDB（或 ACE）提供程序。 若要安装此提供程序，请转到“下载页”并安装相关的（32 位或 64 位）版本。 如果尚未安装，连接到旧工作簿时将看到以下错误：

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

不能在云服务环境中安装 ACE。 因此，如果在云主机（如 Power Query Online）中看到此错误，则需要使用已安装 ACE 的网关连接到旧版 Excel 文件。

支持的功能

  • 导入

从 Power Query Desktop 连接到 Excel 工作簿

要从 Power Query Desktop 建立连接：

  1. 在“获取数据”体验中选择“Excel 工作簿”。 Power Query Desktop 中的“获取数据”体验因应用而异。 要详细了解适用于你的应用的 Power Query Desktop“获取数据”体验，请转到在何处获取数据。

  2. 浏览并选择要加载的 Excel 工作簿。 然后选择“打开”。

    从文件资源管理器中选择 Excel 工作簿。

    如果 Excel 工作簿处于联机状态，请使用 Web 连接器连接到工作簿。

  3. 在“导航器”中，选择所需的工作薄信息，然后选择“加载”以加载数据，或是选择“转换数据”以继续在 Power Query 编辑器中转换数据。

    导入到 Power Query Desktop 导航器中的 Excel 工作簿。

从 Power Query Online 连接到 Excel 工作簿

要从 Power Query Online 建立连接：

  1. 在“获取数据”体验中选择“Excel 工作簿”选项。 不同的应用通过不同的方式来使用 Power Query Online“获取数据”体验。 要详细了解如果从你的应用访问 Power Query Online“获取数据”体验，请转到在何处获取数据。

    突出显示了 Excel 工作簿的“获取数据”窗口的屏幕截图。

  2. 在显示的 Excel 对话框中，提供 Excel 工作簿的路径。

    用于访问 Excel 工作簿的连接信息的屏幕截图。

  3. 必要时选择本地数据网关来访问 Excel 工作簿。

  4. 如果这是首次访问此 Excel 工作簿，请选择身份验证类型并登录到帐户（如果需要）。

  5. 在“导航器”中，选择所需的工作簿信息，然后选择“转换数据”以继续在 Power Query 编辑器中转换数据。

    导入 Power Query Online 导航器的 Excel 工作簿的屏幕截图。

建议的表

如果连接到未具体包含单个表的 Excel 工作簿，Power Query 导航器将尝试创建一个建议的表列表供你从中选择。 例如，请考虑以下工作簿示例，其中包含从 A1 到 C5 的数据、从 D8 到 E10 的更多数据，以及从 C13 到 F16 的更多数据。

包含三组数据的 Excel 工作簿的屏幕截图。

连接到 Power Query 中的数据时，Power Query 导航器会创建两个列表。 第一个列表包含整个工作簿的工作表，第二个列表包含三个建议的表。

如果在导航器中选择整个工作表，则工作簿如 Excel 中显示的那样，其中所有空白单元格填充为 null。

导航器的屏幕截图，其中显示了空单元格中为 null 的单个工作表。如果选择其中一个建议的表，Power Query 能够从工作簿布局中确定的每个表将显示在导航器中。 例如，如果选择表 3，则会显示最初出现在单元格 C13 到 F16 中的数据。

导航器的屏幕截图，包含已选择的“建议表”下的表 3，并显示了表 3 的内容。

注意

如果工作表更改足够大，则表可能无法正确刷新。 可以通过再次导入数据并选择建议的新表来解决刷新问题。

故障排除

数值精度（或“为什么我的数字发生了变化？”）

导入 Excel 数据时，你可能会注意到某些数值在导入 Power Query 时似乎略有更改。 例如，如果在 Excel 中选择包含 0.049 的单元格，此数字将在编辑栏中显示为 0.049。 但是，如果将同一单元格导入 Power Query 并选中，预览详细信息将显示为 0.0490000000000000002（在预览表中，其格式则为 0.049）。 这是怎么回事？

答案有点复杂，想必与 Excel 如何使用二进制浮点表示法来存储数字有关。 底部行有一些 Excel 无法以 100% 精度表示的数字。 如果想破解这一谜题，请打开 .xlsx 文件并查看正在存储的实际值，你将看到，在 .xlsx 文件中，0.049 实际存储为 0.049000000000000002。 这是 Power Query 从 .xlsx 读取的值，也是在 Power Query 中选择单元格时显示的值。 （有关 Power Query 中的数值精度详细信息，请转到 Power Query 中的数据类型的“十进制数”和“固定十进制数”部分。

连接到联机 Excel 工作簿

如果要连接到 Sharepoint 中托管的 Excel 文档，可以通过 Power BI Desktop、Excel 和数据流中的 Web 连接器以及数据流中的 Excel 连接器执行此操作。 若要获取指向该文件的链接，请执行以下操作：

  1. 在 Excel Desktop 中打开文档。
  2. 打开“文件”菜单，选择“信息”选项卡，然后选择“复制路径”。
  3. 将地址复制到“文件路径或 URL”字段中，并从地址末尾删除 ?web=1。

旧版 ACE 连接器

Power Query 使用 Access 数据库引擎（或 ACE）OLEDB 提供程序读取旧工作簿（如 .xls 或 .xlsb）。 因此，导入 OpenXML 工作簿（如 .xlsx）时不会发生导入旧工作簿时可能遇到的意外行为。 下面是一些常用示例。

意外的值格式设置

由于 ACE，旧版 Excel 工作簿中导入的值的精度或保真度可能低于预期。 例如，假设 Excel 文件包含数字 1024.231，已将其显示格式设置为“1,024.23”。 导入 Power Query 时，此值表示为文本值“1,024.23”，而不是基础全保真数字 (1024.231)。 这是因为在这种情况下，ACE 不会向 Power Query 显示基础值，而只会显示 Excel 中显示的值。

意外的 null 值

当 ACE 加载表时，它会查看前八行以确定列的数据类型。 如果前八行不代表后面的行，ACE 可能会为该列应用不正确的类型，并为任何与类型不匹配的值返回 null。 例如，如果列包含前八行中的数字（如 1000、1001 等），但在后面的行（如“100Y”和“100Z”）中具有非数值数据，则 ACE 得出结论，该列包含数字，并且任何非数值都返回为 null。

值格式不一致

在某些情况下，ACE 在刷新时返回完全不同的结果。 使用“格式设置”部分介绍的示例，你可能会突然看到值 1024.231 而不是“1,024.23”。 将 Excel 中打开的旧工作簿导入 Power Query 时可能会导致此差异。 若要解决此问题，请关闭工作簿。

缺少或不完整的 Excel 数据

有时 Power Query 无法从 Excel 工作表中提取所有数据。 此失败通常由维度不正确的工作表（例如，实际数据占用三列或 200 行以上时维度为 A1:C200）导致。

如何诊断不正确的维度

查看工作表的维度：

  1. 使用 .zip 扩展名重命名 xlsx 文件。
  2. 在文件资源管理器中打开文件。
  3. 导航到 xl\worksheets。
  4. 将有问题的表（例如 Sheet1.xml）的 xml 文件从 zip 文件复制到另一个位置。
  5. 检查文件的前几行。 如果文件足够小，请在文本编辑器中将其打开。 如果文件太大而无法在文本编辑器中打开，请从命令提示符运行以下命令：more Sheet1.xml。
  6. 查找 <dimension .../> 标记（例如 <dimension ref="A1:C200" />）。

如果文件具有指向单个单元格（例如 <dimension ref="A1" />）的维度属性，Power Query 将使用此属性查找表上数据的起始行和列。

但是，如果文件具有指向多个单元格（如 <dimension ref="A1:AJ45000"/>）的维度属性，Power Query 将使用此范围查找起始行和列以及结束行和列。 如果此范围不包含表中的所有数据，则不会加载某些数据。

如何修复不正确的维度

可以通过执行以下操作之一来修复由错误维度引起的问题：

  • 在 Excel 中打开并重新保存文档。 此操作将使用正确的值覆盖文件中存储的错误维度。

  • 确保生成 Excel 文件的工具已修复，以便正确输出维度。

  • 更新 M 查询以忽略不正确的维度。 自 2020 年 12 月 Power Query 发布以来，Excel.Workbook 现在支持一个 InferSheetDimensions 选项。 如果为 true，此选项将导致该函数忽略工作簿中存储的维度，而是通过检查数据来确定它们。

    以下示例演示了如何提供此选项：

    Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

加载 Excel 数据时运行缓慢或性能缓慢

Excel 数据加载速度缓慢也可能由维度不正确引起。 但是，在这种情况下，速度缓慢是由于维度太大，而不是太小。 维度过大将导致 Power Query 从工作簿中读取的数据量比实际需要的大得多。

若要解决此问题，请参阅“查找并重置工作表上的最后一个单元格”获取详细说明。

从 SharePoint 加载数据时性能不佳

从计算机上的 Excel 或 SharePoint 检索数据时，请考虑所涉及的数据量以及工作簿的复杂性。

你会注意到从 SharePoint 检索非常大的文件时性能会下降。 但是，这只是问题的一部分。 如果在从 SharePoint 检索的 Excel 文件中具有重要的业务逻辑，则刷新数据时，可能需要执行此业务逻辑，这可能会导致复杂的计算。 考虑聚合和预先计算数据，或将更多业务逻辑移出 Excel 层并移入 Power Query 层。

使用 Excel 连接器导入 CSV 文件时出错

即使 CSV 文件可以在 Excel 中打开，但它们不是 Excel 文件。 请改用文本/CSV 连接器。

导入“Strict Open XML 电子表格”工作簿时出错

导入以 Excel 的“Strict Open XML 电子表格”格式保存的工作簿时，可能会看到以下错误：

DataFormat.Error: The specified package is invalid. The main part is missing.

主机上未安装 ACE 驱动程序时会发生此错误。 以“Strict Open XML 电子表格”格式保存的工作簿只能由 ACE 读取。 但是，由于此类工作簿使用与常规 Open XML 工作簿 (.xlsx) 相同的文件扩展名，因此无法使用该扩展名来显示寻常的 the Access Database Engine OLEDB provider may be required to read this type of file 错误消息。

若要解决此错误，请安装 ACE 驱动程序。 如果在云服务中发生此错误，则需要使用在安装了 ACE 驱动程序的计算机上运行的网关。

“文件包含损坏的数据”错误

导入某些 Excel 工作簿时，可能会看到以下错误。

DataFormat.Error: File contains corrupted data.

通常此错误表示文件格式存在问题。

但是，有时当文件似乎是 Open XML 文件（如 .xlsx），但实际需要 ACE 驱动程序来处理该文件时，会发生此错误。 若要详细了解如何处理需要 ACE 驱动程序的文件，请转到“旧版 ACE 连接器”部分。

已知问题和限制

  • Power Query Online 无法访问加密的 Excel 文件。 由于标记为除“公开”或“非业务”以外其他敏感类型的 Excel 文件已加密，因此无法通过 Power Query Online 访问这些文件。
  • Power Query Online 不支持受密码保护的 Excel 文件。
  • Excel.Workbook useHeaders 选项使用当前区域性将数字和日期转换为文本，因此在设置了不同操作系统区域性的环境中运行时行为不同。 建议改用 Table.PromoteHeaders 。