错误处理
与 Excel 和 DAX 语言使用 IFERROR
函数的方式类似,Power Query 也有自己的语法来测试和捕获错误。
如处理 Power Query 中的错误的文章中所述,错误会出现在步骤级别,也会出现在单元格级别。 本文重点介绍如何根据自己的特定逻辑捕获和管理错误。
备注
为了演示这个概念,本文使用 Excel 工作簿作为数据源。 此处展示的概念适用于 Power Query 中的所有值,而不仅仅是来自 Excel 工作簿的值。
此演示的示例数据源是包含下表的 Excel 工作簿。
Excel 工作簿中的此表在标准费率列中出现 Excel 错误,例如 #NULL!、#REF! 和 #DIV/0!。 将此表导入 Power Query 编辑器时,下图显示了其外观。
请注意 Excel 工作簿中的错误显示方式以及每个单元格中的[Error]
值。
本文介绍如何将错误替换为另一个值。 此外,还将了解如何捕获错误并将其用于自己的特定逻辑。
在本例中,目标是在示例数据源中创建一个新的“最终费率”列,该列将使用“标准费率”列中的值。 如果存在任何错误,则它使用对应“特殊速率”列中的值。
若要创建新的自定义列,请转到添加列菜单,然后选择自定义列。 在自定义列窗口中,输入公式 try [Standard Rate] otherwise [Special Rate]
。 将此新列命名为最终速率。
此公式会尝试评估“标准费率”列,如果没有发现错误,则输出其值。 如果在“标准费率”列中找到了错误,则输出将是otherwise
语句后定义的值,在本例中为“特殊费率”列。
将正确的数据类型添加到表中的所有列后,下图显示了最终表的外观。
备注
作为替代方法,还可以输入与上一个公式等效的公式 try [Standard Rate] catch ()=> [Special Rate]
,但对不需要参数的函数使用 catch 关键字。
该catch
关键字于 2022 年 5 月引入 Power Query。
使用与上一部分相同的示例数据源,新目标是为最终费率创建新列。 如果“标准费率”中存在值,则使用该值。 否则将使用“特殊速率”列中的值,但包含任何 #REF!
错误的行除外。
备注
排除#REF!
错误的唯一目的是为了方便演示。 使用本文中展示的概念,可以从错误记录中选择任何字段作为目标。
当选择错误值旁边的任意空白时,屏幕底部会显示详细信息窗格。 详细信息窗格包含错误原因DataFormat.Error
和错误消息Invalid cell value '#REF!'
:
一次只能选择一个单元格,因此一次只能有效地检查一个错误值的错误组件。 此时,你将创建一个新的自定义列并使用 try
表达式。
若要创建新的自定义列,请转到添加列菜单,然后选择自定义列。 在自定义列窗口中,输入公式 try [Standard Rate]
。 将此新列命名为所有错误。
try
表达式将值和错误转换为指示 try
表达式是否处理错误的记录值,以及正确的值或错误记录。
可以使用记录值展开此新建的列,并通过选择列标题旁边的图标来查看可展开的可用字段。
此操作会公开三个新字段:
- All Errors.HasError—显示标准速率列中的值是否包含错误。
- All Errors.Value — 如果“标准速率”列中的值没有错误,则此列显示“标准费率”列中的值。 对于有错误的值,此字段不可用;且在展开操作期间,此列将具有
null
值。 - All Errors.Error — 如果“标准速率”列中的值有错,则此列显示“标准费率”列中值的错误记录。 对于无错误的值,此字段不可用;且在展开操作期间,此列具有
null
值。
若要进一步调查,可以展开 All Errors.Error 列,以获取错误记录的三个组件:
- 错误原因
- 错误消息
- 错误详细信息
执行展开操作后,All Errors.Error.Message 字段将显示特定的错误消息,告知您每个单元格有哪些 Excel 错误。 错误消息派生自错误记录的错误消息字段。
现在,根据新列中的每条错误消息,可以使用名称最终速率和以下子句创建新的条件列:
- 如果“All Errors.Errors.Message”列中的值等于
null
,则输出将是“标准费率”列中的值。 - 但是,如果“All Errors.Errors.Message”列中的值不等于
Invalid cell value '#REF!'.
,则输出将是“特殊费率”列中的值。 - Else, null.
仅保留帐户、标准费率、特殊费率和最终费率列,并为每列添加正确的数据类型后,下图显示了最终表的外观。
或者,也可以使用 try
和 catch
关键字创建新的自定义列。
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null