Power Query 真经 - 第 3 章 - 数据类型与错误

2022-04-02 12:52:28 浏览数 (1)

在 Power Query 中，未等做事就遇到各种错误是十分常见的，本章将扫平这些障碍，为进一步进行实质性的数据转换提供帮助。 BI佐罗

本章专门讨论 Power Query 新手会面临的两个常见问题：理解 Power Query 是基于数据类型（而不是数据格式）的工具，以及如何理解和处理 Power Query 查询中的错误。

3.1 数据类型与格式

在现场课程、博客评论和论坛帖子中经常收到的一个问题是：“如何在 Power Query 或 Power BI 中格式化数据？” 简短的回答是：从没这个需求，但更详尽的答案是关于数据类型与格式的理解。

3.1.1 数据格式

为了说明这个问题，快速看一下工作簿中 Excel 表中的一些示例数据：“第 03 章 示例文件 Data Types vs Formats.xlsx”，如图 3-1 所示。

图 3-1 Excel 中的示例数据

在这里看到的是在 Excel 中已被格式化的数据。虽然表格中显示的每个数值都被四舍五入到第一列中显示的小数位数，但会注意到它们都被格式化为显示 6 位小数。这里需要认识到的关键区别是，“Whole（整数）” 列中的第一个值是【整数】数据类型，其值为 9553 ，已被格式化显示为 9,553.000000 。

为什么这很重要？如果让德国人格式化这个数据，他们不会选择以同样的方式显示这个值。他们会用 9.553,000000 的格式来显示这个值。数值没有改变，但数据显示的方式改变了。

【注意】 格式仅指定数据的显示方式，而不会以任何方式影响其数值本身或精度。

3.1.2 数据类型

虽然这种情况在 Excel 365 中已经开始改变，但 Excel 在其历史上一直把数据类型和格式化当作非常相似的东西。如果查看一下 Excel 显示的真实数据类型，大概包括这个列表。

  1. 数字。
  2. 文本。
  3. 空白。
  4. 错误。
  5. 布尔值（True/False）。

日期实际上是数字值，代表自 1900 年 1 月 1 日以来的天数，格式化为可以识别的日期。时间也是十进制值（一天的小数部分），格式化为时间格式来显示。

Power Query 有五种主要的数据类型，如下所示。

  1. 数值型。
  2. 日期和时间。
  3. 文本。
  4. 布尔型（True/False）。
  5. 二进制（文件）。

但在前两个类别中，还存在其他数据子类型。另一件需要注意的事情是，这些数据类型中的每一个都是不同的，这将对用户如何从一种数据类型转换到另一种数据类型产生影响，图 3-2 显示 Power Query 数据类型。

图 3-2 Power Query 的数据类型

此时，需要认识到的重要一点是，这些数据类型都只与定义数据类型有关，而不是如何格式化数据。想要理解这一点，看看当样本数据表导入 Power Query 时会发生什么。

  • 显示【查询和连接】窗格（【数据】【查询 & 连接】）。
  • 双击 “DataTypes” 查询，打开 Power Query 编辑器。
  • 选择 “Whole” 列第三行的单元格。此时，关于查询有如下三点值得注意，如图 3-3 所示。
    1. 列的左上方的【数据类型】图标都显示为 “ABC123”：这是一个在前面的列表中没有包含的数据类型。这种数据类型的正式名称是【任意（any）】，表明该列的数据类型还没有定义，或者说该列中可能有混合的数据类型。
    2. 此时选择的单元格包含一个 9350.095 的值。尽管这一行的其他数值有四位小数，但只需要三位小数就可以显示出此值真实的数值，所以这就是 Power Query 显示数据的方式。
    3. 通过选择一个数据单元，Power Query 在窗口的左下角会显示该单元格的内容预览。这是很方便，因为它有更多的空间来显示较长的文本字符串，甚至包含可选择的不可见文本（让用户发现一个字符串的前后否有空格）。

图 3-3 怎么能把一个会计逼疯？确保小数点没有对齐就可以了

在这里要认识到的是，Power Query 展示的是 “原始数据”，没有定义任何数据类型。定义这些列的数据类型，从前两列开始。

  1. 单击 “Precision” 列上的【ABC123】图标，选择【整数】类型。
  2. 改变 “Whole” 列的数据类型（使用和 1 同样的步骤）。
  3. 选择与之前的同一个单元格进行预览。

注意到有什么不同吗？比如 9350.095 这个值实际上已经被更改为 9350，不仅仅是在顶部列中，而且在窗口底部的数据预览中也同样改变了，如图 3-4 所示。

图 3-4 9350.095 已经被四舍五入到了零位小数

通过将数据类型设置为【整数】，Power Query 通过将一些数据点四舍五入为一个【整数】值来更改它们。如果是故意这样的，那么没有问题。但是，如果仍然需要小数点的精度，以便以后得到精确的汇总值，而且这样做只是为了格式化，那么这个操作反而是错的，因为这些值已经失去了该有的精度。

接下来是设置 “Currency” 列的数据类型。

  1. 单击 “Currency” 列的【ABC123】图标，选择为【货币】类型（Power BI 中的固定小数位的十进制数）。
  2. 选择 “Currency” 列最后一行的单元格进行预览。

关于 “Currency” 列的显示，需要注意：与最初的数值不同，这一列的格式现在是显示两位小数。版本不同的 Power Query 的显示略有不同，但在整个列中是一致的，显示两个小数位。

【注意】 有趣的是，在 Power Query 的早期实现中，【货币】（固定小数）数据类型并不包含特殊的格式化显示。换句话说，1603 的值会在没有小数的情况下直接显示出来，不带有小数点。由于社区的用户反馈，微软改变了这一点，不仅应用了数据类型，而且还基于 Windows 控制面板中的设置添加了货币格式，如图 3-5 所示。

图 3-5 【货币】（固定小数）数据类型影响精度并增加格式化

关于这种数据类型，需要认识的重要一点是，作为一种数据类型，它的主要工作是处理数值的精度，这一点可以在 “Currency” 列的最后一行中看到。如果检查左下角的数值预览，会发现数据点的真实值是 2951.8819 ，而该列的格式显示为 2951.88。将其与原始值 2951.881907 进行比较，可以看出这个值已经被四舍五入到了四位小数。

【注意】 尽管【货币】（固定小数）数据类型也包括显示两位小数的格式，但它将数据点四舍五入到小数点后四位。如果这看起来很奇怪，请想想外币汇率，它被带到了小数点后四位。

此时，探讨的最后一种数字数据类型是【小数】数据类型，将把它应用到 “Decimal” 列中。

  1. 单击 “Decimal” 列的 【ABC123】选择为【小数】类型。
  2. 选择 “Decimal” 列最后一行的单元格进行预览。

在这种情况下，会注意到数值是以其全部精度显示的，没有四舍五入，也没有任何额外的格式化。任何尾数为 0 的小数都不会显示，只显示出数值所需的字符数。这可以通过检查每个值的预览来，并确认到在 “Decimal” 列的每个单元格中看到的值与选择给定单元格时出现的值预览一致来证明这一点，如图 3-6 所示。

图 3-6【小数】数据类型保留了所有的十进制值

这里需要注意的一件重要的事情是，数据类型和格式完全不是一回事。

  1. 格式：控制一个数字的显示方式，而不以任何方式影响基础精度。
  2. 数据类型：控制数据的类型，将更改数值的精度，使之与所声明的数据类型一致。

这显然是一个非常重要的区别，应该注意到。设置数据类型可以（而且经常）以某种方式改变基础值，而格式化永远不会。

3.1.3 如何设置格式

简而言之，在 Power Query 中不需要设置格式。

在数据类型与格式之争中，查询编辑器的主要作用是设置数据类型，而不是格式化数据。为什么呢？因为无论如何没有人会在查询编辑器中读取数据。这个工具的作用是获得正确的数据，而不是呈现数据。最终，将会把数据加载到如下两个地方之一。

  1. Excel：工作表或 Excel 数据模型。
  2. Power BI：数据模型。

【注意】 本书的重点是 Excel 和 Power BI。如果是在不同的微软产品中使用 Power Query，则可能有额外的或不同的加载目的地。

数据的格式化应该在展示层中应用。这意味着在以下一个（或多个）地方会被用到。

  1. 工作表单元格：无论是在表格、数据透视表还是 CUBE 函数（译者注：一种可以从数据模型中提取值的 Excel 函数）中，如果它位于 Excel 网格中，则可以对数据应用数字格式。
  2. 度量值格式（如果数据被加载到数据模型中）：在 Excel 中，这可以通过在创建度量值时设置默认的数字格式来控制，而在 Power BI 中，通过选择度量值然后在建模标签上设置格式来配置。
  3. 图表或视觉显示效果：在 Excel 中，则可以强制数字格式以需要的方式出现在图表中，在 Power BI 的可视化格式工具中也有类似的选项。

3.1.4 设置数据类型的顺序

由于数据类型的更改会影响数值的精度，因此认识到 “Changed Type” 步骤的顺序非常重要。为了演示这一点，需要进行如下操作，结果将如图 3-7 所示。

  1. 确保在【应用的步骤】窗格中仍然选择了 “Changed Type” 步骤。
  2. 单击 “Whole” 列上的【123】图标更改为【小数】。
  3. 当出现 “Changed Type” 的提示时，选择【添加新步骤】（而不是【替换当前转换】）。

图 3-7 如果 “Whole” 现在是小数，那么小数去哪儿了呢

通常情况下，当对一个列应用数据类型时，Power Query 只执行给到它的操作，根本不会进行提示。如果选择了一个 “Changed Type” 步骤，并试图更改步骤中已经包含的列上的数据类型的情况下，将会得到一个选择，要么按照【替换当前转换】步骤的配置，要么【添加新步骤】。这一选择将产生完全不同的结果。

当选择【添加新步骤】时，首先会计算之前 “Changed Type” 的结果，然后根据这些值应用新的数据类型。基于上面采取的步骤，有效地连接到数据，并将 “Whole” 列中的数值四舍五入为整数，删除所有小数。然后，将该列的数据类型更改为【小数】数据类型。问题是小数已经不存在了，因为数值已经在上一步被四舍五入而转换为整数了。

相反，如果选择的是【替换当前转换】而不是【添加新步骤】，结果将会大不相同。它不会在原来的 “Changed Type” 中应用【整数】数据类型，而是将步骤更新为使用【小数】数据类型，小数的精度将被保留下来。

【警告】 需要记住的是，操作步骤的顺序非常重要，在每次导入数据时 Power Query 都会自动应用 “Changed Type” 的步骤，所以每次都做检查是一个很好的习惯。默认情况下，Power Query 在设置数据类型时只预览前 1000 行，这意味着如果数据集中的第一个小数值显示在第 1001 行，Power Query 将选择一个【整数】数据类型，在导入时对该列的所有行进行四舍五入。即使在查询后面的一个新步骤中更正了数据类型，此时这些值也已经被四舍五入了。

【注意】 可能会想知道为什么 Power Query 不直接覆盖上一步而无需询问。答案是，有些数据类型在转换成另一种格式之前必须先转换成另一种格式。这方面的一个例子是，当想要将基于文本的日期与时间转换为只有日期的情况：如果要将 “2012-12-23 12:05 PM” 转换为【日期】，必须先将其转换为【日期 / 时间】，再将【日期 / 时间】转换为【日期】。

3.1.5 数据类型的重要性

既然无论如何都要在 Excel 或 Power BI 中格式化数据，而错误地选择数据类型会影响数据的精确性，那么能不能不在 Power Query 中设置数据类型呢？

答案是否定的。

需要声明数据类型的第一个原因是，所有的 Power Query 函数都需要输入特定数据类型，而且，与 Excel 不同的是 Power Query 不会隐式地从一种数据类型转换为另一种数据类型。如果有一个已经被设置为数值型数据类型的列，用户试图对其使用一个需要文本输入的命令，由于数据类型不匹配，会收到一个错误。

第二个原因是，未定义的数据类型【任意】（显示为 ABC123 图标）允许程序在使用时做出最佳猜测。虽然这在某些情况下可以工作，但在数据类型仍然定义为【任意】数据类型的情况下，将数据加载到工作表或数据模型中是非常危险的。为什么呢？来看一个查询，以及当数据以未定义数据类型的列加载时会发生什么，如图 3-8 所示。

图 3-8 根据加载目的地的不同，可以对数据进行不同的解释

Query：查询 Undefined Dates：（这里的内容不需要替换） Worksheet：工作表 Undefined Dates：（这里的内容不需要替换） Data Model：数据模型 Undefined Dates：（这里的内容不需要替换）

图 3-7 查询中有一个【ABC123】的未定义数据类型，但 “Undefined Dates” 列看起来像日期。它们甚至是斜体的，这似乎表明它们确实是日期。

如果将数据直接加载到 Excel 工作表中。在没有定义数据类型的情况下，Power Query 会对所需要的数据做出最佳猜测，所以它返回了一列数值（这些表示给定日期的日期序列号）。

然而，如果在数据加载时，勾选数据模型，现在的输出看起来不错，不是吗？可以在 Excel 网格中看到的问题是，数据是左对齐的，原因是这些数据的类型不是日期类型，而是文本类型。事实上，如果检查数据模型，是可以确认这些日期确实是作为文本加载的。

【注意】 Power BI 也不能避免这个问题。它利用数据模型来存储数据，所以它将把未定义数据类型的日期加载为文本，就像 Excel 的数据模型一样。

这是未定义数据类型的真正危险所在。Power Query 仍然对使用【任意】数据类型定义的列应用一种格式，但这并不意味着数据类型已经被定义。无论查看上面的哪个版本，这都不是想要的结果，更糟糕的是，仅仅更改加载目的地就会影响输出的结果。

【注意】 在本书的后面，将接触到添加或合并表等转换。这些可以将不同数据集中的数据合并到同一列中。如果数据的类型不同，则会发现这时的列会重置为【任意】数据类型。

【警告】 不要引火烧身，一定要确保任何加载到工作表或数据模型的查询的最后一步都是重新定义数据类型。

3.2 常见的错误类型

在 Power Query 中，有如下两种类型的错误，它们以不同的方式表现出来。

  1. 步骤级错误：这些错误发生在步骤级别，不仅阻止了特定步骤的执行，而且也阻止了任何后续步骤的执行。当查询根本无法加载时，将会发现查询中存在一个步骤级错误。
  2. 值错误：这些错误发生在单元格层面。查询仍将加载，但错误值将显示为空白值。

要了解这些错误是如何显示的，以及如何解决这些问题，请打开以下示例文件：

“第 03 章 示例文件 / ErrorTypes.xlsx”。

工作表中有一个漂亮的表格，到目前为止，一切看起来都运行得很好，如图 3-9 所示。

图 3-9 基于 “ErrorData” 查询的输出表

到目前为止一切都很好，但现在是时候引出一些问题了。

  1. 转到【数据】选项卡，单击【全部刷新】（在 Power BI 中是【刷新】）。

将立即得到一个错误提示，表示根本找不到源数据文件，如图 3-10 所示。

图 3-10 对不起，你不能刷新这个文件

由于它阻止了文件的加载，所示此时正在处理一个步骤级错误。

3.4 步骤级错误

在 Power Query 中触发两个最常见的步骤级错误如下所示。

  1. 无法找到数据源。
  2. 无法找到列名。

为了使刷新工作正常进行，需要编辑查询，找到显示这个问题的步骤，并找出面前提到的错误类型。只有这样，才能够修复它。

  1. 显示【查询 & 连接】窗格（如果它还没有显示，则单击【数据】【查询和连接】）。
  2. 右击 “ErrorData” 查询【编辑】。

【注意】 在处理错误时，最好总是单击 Power Query 编辑器【主页】选项卡上的【刷新预览】按钮。这将确保没有使用预览的缓存版本，因为缓存版本不会显示正在查找的错误。

3.4.1 数据源错误

默认情况下，在编辑一个查询时，将默认选择该查询最后一步。此时，步骤级错误变得非常明显，因为如果查询中存在步骤级错误，Power Query 将在主预览区显示一条大的黄色信息，来代替数据，如图 3-11 所示。

图 3-11 此时遇到了一个问题：步骤级错误

关于这个错误信息，有如下几点需要注意。

  1. 它以具体的错误类型开始。在这种情况下，有一个数据源错误，表明 Power Query 无法找到数据源文件。
  2. 它提供了一个详细信息区域，指出导致错误的具体内容。在本例中，它是丢失文件的完整文件路径。
  3. 有一个【转到错误】的按钮。若当前步骤不是错误的直接来源时，这个按钮就会出现。

在大多数情况下，当单击【转到错误】按钮时，将被直接带入导致错误的步骤。然而，在这种情况下，将转到 “Promoted Headers” 步骤。而当试图单击齿轮图标来重新配置该步骤时，它提示：“我们无法修改此步骤，因为前面的步骤中存在错误。请先解决这些错误”。如图 3-12 所示。

图 3-12 如果前面的步骤包含一个步骤级的错误，则无法重新配置步骤

这将被归类为一种错误，在练习本书文件时，就会大量出现并被修复。然而，如果这种情况发生的时候，需要知道如何处理它。答案是相当直接的，只要继续单击上一步，直到发现是哪个步骤导致的错误，或者直到进入查询的第一步，如图 3-13 所示。

图 3-13 查询的第一步表明它导致了错误

这种类型的错误非常常见，特别是在与同事共享 Power Query 解决方案时，因为文件路径总是硬编码的。像桌面和下载这样的个性化文件夹在文件路径中包含本机用户名，甚至网络驱动器也可以映射到不同人的不同字母。事实上，本书的每一个完成的示例文件都会展示出这个问题，因为用户不会把数据文件存储在与我们一样的地方。

实际上有如下所示三个不同的选项可以更新文件路径。

  1. 单击 “Source” 步骤旁边的齿轮图标。
  2. 单击错误信息中的【编辑设置】按钮。
  3. 进入【主页 】【数据源设置】选择【更改源】。

【注意】 实际上，无需进入 Power Query 编辑器就可以访问数据源设置对话框。在 Excel 中，可以在【数据】选项卡上的【获取数据】菜单的底部附近找到【数据源设置】。在 Power BI 中，可以在【主页】选项卡的【转换数据】区域中找到【数据源设置】。

无论选择那种方法，都会被带到浏览和更新文件路径的窗口。现在就这样做吧，找到并选择这里显示的数据文件：“第 03 章 示例文件 ErrorData.csv”。

更改完成后，现在应该可以看到预览区域填充了值。

【警告】 前两种方法只更新所选查询的数据源，而最后一种方法有一个好处，它将更改数据源的所有实例，即使它被用于多个查询中。尽管如此，还需要单击【刷新预览】，以使编辑器认识到数据源已经更新了。

3.4.2 没有找到某列

在这一点上，需要来触发另一个步骤级错误。

  1. 选择 “Promoted Headers” 步骤。
  2. 双击 “Item Name” 列，将其重命名为 “Item”。
  3. 在弹出的【插入步骤】对话框单击【插入】。
  4. 选择 “Changed Type” 步骤。

注意到了吗，数据预览失败，并看到了一个错误。这是一个步骤级错误，它再次阻止数据加载。然而，这一次的错误原因与之前的略有不同，如图 3-14 所示。

图 3-14 表达式错误，表明缺少一个列

在许多方面，这个错误甚至比前面显示的文件路径错误更常见。不管它是如何造成的，它表明在这个步骤中提到的一个列在前一个步骤中已经不存在了。在这种情况下，Power Query 试图在 “Item Name” 列上设置数据类型，但是该列已经不存在了，因为它在上一步中已经被重命名为不同的名称了。

虽然这类错误可能出现在很多地方，但到目前为止，最常看到它的地方是在 “Changed Type” 步骤中。这是因为 “Changed Type” 步骤将列名硬编码到它的公式中。如果在查询前面的步骤中发生了任何事情，导致列被重新命名、删除或不再存在，那么在硬编码其中的列名的任何步骤中，最终都会遇到这个问题。

那么如何才能解决这个问题呢？同样，有如下的一些选择。

  1. 删除这个步骤，并根据先前步骤的当前状态重新创建它。
  2. 调整前面的步骤，以确保列名仍然存在。
  3. 删除之前导致列不再存在的任何步骤 。
  4. 通过公式动态计算，增加或删除列。

当用户读完本书时，会掌握最后一个堪称完美的方法，但现在先简单处理。这一步只是为了破坏查询而插入的，所以把它去掉吧，结果将如图 3-15。

  1. 单击 “Renamed Columns” 步骤旁边的 “x” 来删除它。
  2. 选择 “Changed Type” 步骤，来验证数据预览是否有效。

图 3-15 一切看起来都很好

【注意】 在绝大多数情况下，删除引发步骤级别错误的 “Changed Type” 步骤是安全的。此时，问问自己是否真的需要在它原来的地方重新应用它，或者在查询结束时重新定义所有数据类型是否是一个更好的选择。

3.5 值错误

虽然步骤级错误绝对是 Power Query 中最严重的错误，但它们不是用户将在 Power Query 中遇到的唯一错误。另一个常见的错误类型是值错误。这些错误实际上也可能更危险，因为它们并不总是那么明显。

值错误最常的是由以下两种情况之一引起的。

  1. 无效的数据类型转换。
  2. 用不兼容的数据类型执行操作。

一起来看看触发这些问题有多容易。

3.5.1 发现错误

如果看这个数据的 “Units Sold” 列，将会注意到在这个列中遇到了一些挑战，如图 3-16 所示。

图 3-16 似乎 “Units Sold” 列中有一些错误

在这种情况下，可以清楚地看到 “Units Sold” 位列中的第 2 行到第 4 行包含一个 “Error”，但现实情况是，错误数据并不总是显示在数据预览中。那么如何识别列中存在的错误？

如果使用的是 Power BI 或 Excel 365 ，将会注意到，在列的标题下有一条红色的短线，后面跟着条纹。这是一个视觉提示，表示该列中存在某种错误。

此外，如果想看到关于列的更多细节，可以在【视图】选项卡中查看和更改这些设置。

  1. 【列质量】。
  2. 【列分发】。
  3. 【列配置文件】。

开启这些设置后，会发现在列的顶部有一些快速的统计数据和图表，它们是旨在帮助评估数据质量，如图 3-17 所示。

图 3-17 在列上显示的列质量指标

【列质量】设置提供了前三个要点，而【列分发】则提供了图表，显示了数据集中不同的（个别的）和唯一（只出现在一行）值的数量。最后一个设置，【列配置文件】当选择一整列时，在屏幕底部提供了一个更详细的视图。

【注意】 如果检查 Power Query 窗口底部的状态栏，会看到【基于前 1000 行的列分析】这句话。这不是很明显，但这些字是可以单击的，将允许更改分析范围：【基于整个数据集的列分析】，而不是默认的 1000 行。

将会注意到，一些统计数据和图表没有显示在 “Units Sold” 列中。这是预料之中的，因为列中有错误。一旦处理了这些错误，它将显示与其他列类似的统计数据。

【注意】 由于这些项目往往要占用大量的空间，在工作中通常会取消勾选【列质量】和【列分发】的复选框 ，但勾选（保留）【列配置文件】。这样就可以在需要的时候通过选择单列来查看统计数据，但在数据预览窗口中为数据留下更多的空间。

【警告】 在 Excel 2019 或更早的版本中不存在这些特性。如果没有这些视觉提示，需要向下滚动列来查看是否存在任何错误。

3.5.2 无效的数据类型转换

现在知道在这一列中至少有一个错误，如何才能找出原因呢？

这个问题的答案是选择单元格并检查预览中出现的信息。然而，在这样做之前，需要注意的是：单击单元格位置的不同，其功能也会不同。

  1. 如果单击单元格中的 “Error” 一词，Power Query 将为查询添加一个新的步骤，并钻取到该错误。虽然仍然会看到错误信息，但这样做并不理想，因为会失去预览窗口中的所有其他数据。
  2. 相反，如果单单击 “Error” 关键词旁边的空白区域，Power Query 将在预览区下面显示错误信息的文本。这种方法的好处是，不会失去查询中其他部分的上下文，并且在修复错误后也没有任何额外的步骤需要管理。

一起来看看是什么导致了这个错误。

  1. 单击 “Units Sold” 列中第一个 “Error” 旁边的空白区域，结果将如图 3-18 所示。

图 3-18 单击 “Error” 旁边的空白区域来显示 “结果窗格”

【注意】 如果不小心单击了 “Error” 关键字，并创建了一个新步骤，只要删除它就可以返回到完整的数据预览。

会注意到，错误信息显示在预览下方的 “结果窗格” 中，并指出它是一个 “DataFormat.Error”。这个有点令人失望，因为它与数据的格式没有任何关系，而是这些单元格中的数据与选择的数据类型不兼容导致的。

当应用 “Changed Type” 操作时，Power Query 会尝试获取单元格中提供的值，并根据本机用户的【Windows 区域设置】中为该数据类型定义的格式将其转换为【整数】类型。如果它不能做到这一点，将收到无法转换的错误。虽然在将列设置为【文本】数据类型时很少出现这种错误，但在将列从【文本】更改为几乎任何其他类型时，这种错误就很常见了。

如果检查该列的标题，将会发现数据被设置为【整数】数据类型（由 123 表示），但由于单元格中的值是 “N/A”，所以导致了错误。由于 “N/A” 不能被表示为数字，所以 Power Query 抛出了一个错误。

现在知道了原因，那么该如何解决这个问题呢？

Power Query 的美妙之处在于，对于这个解决数据错误的问题，实际上有多种选择。

  1. 在 “Changed Type” 步骤之前插入一个新的步骤，用 “0” 替换 “N/A”。
  2. 在 “Changed Type” 步骤之前插入一个新的步骤，用 “null” 关键字来替换 “N/A“。
  3. 右击 “Units Sold”【替换错误】用 “0”（或 “null”）。
  4. 选择 “Units Sold”，然后转到【主页 】【删除行】【删除错误】。
  5. 选择所有的列，然后转到【主页】 【删除行】 【删除错误】。

【警告】 在利用删除行之前，建议先浏览整个数据，以确保可以这样做。最谨慎的方法是替换错误，而最强硬的方法是删除任何列中有错误的行。使用哪一种方法完全取决于数据本身。

在查看数据时，当 “Units Sold” 包含 “N/A” 时，似乎触发了错误，考虑到实际的业务，这个错误看起来是可以删除这些行。这里采用相对谨慎的方式来操作，仅删除这列的错误，而不是全部列的错误，这样就不会意外地失去任何可能需要的数据。

选择 “Units Sold”，然后进入【主页】【删除行】【删除错误】。

【待修正】这里文中前后表达的意思不一致。

结果是一个漂亮干净的表格，不再有错误，如图 3-19 所示。

图 3-19 所有的错误都已经从数据集中删除了

3.5.3 不兼容的数据类型

为了快速演示不兼容数据类型的问题，请按照以下步骤创建一个新的列，该列将组乘以 “Units Sold” 如下所示。

  1. 将 “Units Sold” 列的数据类型改为文本。
  2. 进入【添加列】【自定义列】。
  3. 在公式区输入以下公式：
代码语言：javascript复制
[Units Sold] * 10
  1. 单击【确定】。

结果是显示的是每一行都是错误的列，如图 3-20 所示。

图 3-20 在 Power Query 中这个公式不兼容

结果窗格显示了 “Expression.Error”（由试图进行的数学运算触发错误），提示的意思是，不能用数字乘以文本。在 Excel 中可能允许的这样做，因为 Excel 在 “Units Sold” 列乘以 10 之前，会隐含地将 “Units Sold” 列转换为数值类型，而 Power Query 则会反馈：“不，不能这么做”。

【注意】 这条信息的不幸之处在于，从错误信息中看不出两个输入（左边或右边）中哪一个是文本类型，哪一个是数值类型。为了弄清楚这个问题，需要仔细查看添加的 “Added Custom（已添加自定义列）” 步骤中的公式，以及该公式中使用的所有列的数据类型。

虽然有一个公式化的方法来解决这个问题，但这种方法将在本书的后面介绍，就现在而言按如下操作即可。

  1. 删除 “Added Custom” 步骤。
  2. 删除 “Changed Type1” 步骤。

现在，数据应该返回的是一个干净的数据预览，没有错误。

3.6 检查查询错误

由于现在已经解决了与数据源缺失有关的步骤级错误和 “Units Sold” 列中的值错误，那么现在将准备重新加载数据，具体做法如下所示。

  1. 单击【关闭并上载（ Power BI 中是【关闭并应用】）】。

数据应该被加载，但此时得到一个信息，数据总共有 4572 行，其中有 345 个错误，如图 3-21 所示。

图 3-21 等等，不是已经修复了所有的错误吗

3.6.1 发现错误的来源

根据在 Excel 中使用的配色方案，可能无法看到错误计数与加载行的计数是不同的颜色。原因是，这实际上是一个超链接。

  1. 单击错误文本：“345 个错误”。

一旦这样做了，将会被启动 Power Query 编辑器，此时会看到一个名为 “ErrorData 中的错误” 的新查询，如图 3-22 所示。

图 3-22 所以这就是错误的来源

暂时不考虑这个查询的具体机制，它基本上在获取查询时，为表的每一行添加一个行号，然后只保留有错误的行。现在可以很容易地看到，这些错误是从导入的文件的第 3,882 行开始的。这也解释了为什么之前没有看到它们。

为了避免对本机电脑造成过重的负担，Power Query 限制了预览窗口中的数据量，并允许用户根据这些预览来建立自己的查询。当选择加载数据时，Power Query 会将用户构建的模式应用于整个数据集。通过这种方式，它避免了必须预先加载所有数据的负担。这一点很重要，因为这让用户可以使用 Power Query 来连接大量的数据集，如果在转换数据之前必须将所有的数据下载到本机电脑上，这显示不合理。

当预览范围之外的数据出现错误时，这个预览方法的问题就出现了。以前，在 “Units Sold” 列的前 1000 行内就被提醒有数据问题，而 “POS Hour” 列中的问题直到第 3,882 行才表现出来，所以在数据加载之前不会看到它们。

不管怎么说，通过使用这个查询，现在可以确定正在导入的数据格式已经从简单的整数变成了 21:00 格式的数字。在 Power Query 可以将 21:00 转换为时间数据类型的地方，由于 “:” 字符的存在，它不能将其转换为整数。

现在知道了问题的原因，即使是在预览窗口中看不到这个问题，也可以构建一个修复方案。

3.6.2 修复最初查询

要修复最初的查询，需要查看它并检查步骤。按如下所示的步骤可以做到这一点。

  1. 展开【查询】导航器窗格。
  2. 选择 “ErrorData” 查询。

然后，此时可以看到最初查询，并在【应用的步骤】窗口中查看其步骤。这就是棘手的部分了。在哪里可以修复它？先思考一下这个问题。

  1. 此时不希望只是删除这些行。与之前的错误示例不同，这些错误发生在保存有效销售信息的行上，这些信息是需要保留。
  2. 此时其中一个值显示为 21:00 ，而前面的值是 8 到 20 。作为用户凭借此时的界面不会知道 21:00 之后会发生什么。它是恢复到 22 ，还是继续显示 22:00 ？
  3. 实际的错误很可能是由 “Changed Type” 动作触发的。

如果在查询触发 “Changed Type” 的操作之前，把 “:00” 从列中的值中删除呢？这应该是可行的，所以来应用一下吧。

  1. 选择 “Promoted Headers” 步骤。
  2. 右击 “POS Hour” 列【替换值】【插入】。
  3. 【要查找的值】“:00”。
  4. 【替换为】这里什么都不填，默认空白。
  5. 单击【确定】。

【注意】 以这种方式修复错误的问题是，用户无法在预览窗口中看到效果。如果这真的造成了困扰，可以在查询中插入一个临时步骤，从数据集中删除最上面的 “x” 行。在这种情况下，可以选择删除前 3,880 行，这意味着第一个错误会出现在第 2 行中。请确保在完成查询之前删除这个步骤。

此时，要确保这些更改是有效的。最可靠的方法是重新加载查询，并查看希望那些错误信息的数量是否已经消失。

  1. 转到【主页】选项卡，单击【关闭并上载 (关闭并应用)】。

【注意】 也可以回到 “ErrorData 中的错误” 查询并强制刷新预览，但仍然需要等待数据集的加载，为什么不同时将数据集加载到最终目的地呢？

可以从结果中得到两个观察结果，如下所示。

  1. 已经成功地去除了错误。
  2. 并且 “ErrorData 中的错误” 查询在默认情况下被创建为 “仅限连接” 查询。

最后一步是特别幸运的，因为确实不希望把所有的错误行加载到一个单独的工作表中，如图 3-23 所示。

图 3-23 耶，没有错误了

【注意】 请记住，每个数据清洗动作都是唯一的，这就需要理解所应对的数据格式。如果一些新值以 22:01 的形式出现（译者注：而不再是 00 结尾的特征），上述步骤将不起作用。在这种情况下，将需要应用一套不同的数据清洗步骤。

3.6.3 删除错误查询

一旦最初的查询被修复，没有理由在解决方案中保留 “ErrorData 中的错误” 查询。如果遇到新的错误，还会看到错误计数的，到那时再去单击它更合适。

在 Excel 的【查询和连接】窗格或 Power Query 中编辑器【查询】导航器中，都可以通过以下操作删除错误查询。

  1. 选择 “ErrorData 中的错误” 查询并按下 DEL 键。
  2. 右击 “ErrorData 中的错误” 查询，并选择【删除】。

3.7 关于数据类型与错误的最后思考

本章介绍了 Power Query 中数据类型的概念，以及识别、跟踪和处理用户将看到的常见错误。这些概念非常重要，不仅可以调试用户自己的实际解决方案，而且对于让用户有信心尝试本书所演示的技术，并在结果与书中所示不同时进行调试。当然，这些只是一个开始。从处理日期和货币的细节问题，到筛选掉错误的整个过程中，其实还有可能遇到更多问题。

bi error excel undefined 数据类型

0 人点赞