Share via

使用自定义函数

  • 项目

如果你发现自己需要将同一组转换应用于不同的查询或值，那么创建一个可以根据需要多次重复使用的 Power Query 自定义函数可能会很有帮助。 Power Query 自定义函数是从一组输入值到单个输出值的映射，是从本机 M 函数和运算符创建的。

虽然如了解 Power Query M 函数中所述，可以使用代码手动创建自己的 Power Query 自定义函数，但 Power Query 用户界面提供了加快、简化和改进自定义函数创建和管理过程的功能。

本文重点介绍仅通过 Power Query 用户界面提供的此体验，以及如何充分利用此体验。

重要

本文概述如何使用 Power Query 用户界面中可执行的常见转换通过 Power Query 创建自定义函数。 它重点介绍用于创建自定义函数的核心概念，并链接到 Power Query 文档中的其他文章，以提供关于本文中引用的特定转换的详细信息。

从表引用创建自定义函数

注意

以下示例是使用 Power BI Desktop 中找到的桌面体验创建的，也可以使用在 Excel for Windows 中找到的 Power Query 体验来对其进行跟踪。

您可以通过以下下载链接下载本文中使用的示例文件来按此示例进行操作。 为简单起见，本文使用文件夹连接器。 若要了解有关文件夹连接器的详细信息，请转到文件夹。 此示例的目的是创建一个自定义函数，在将所有文件中的所有数据合并到单个表中之前，此函数可应用于该文件夹中的所有文件。

首先使用文件夹连接器体验导航到你的文件所在的文件夹，然后选择转换数据或编辑。 这些步骤将带你了解 Power Query 体验。 右键单击内容字段中所选择的二进制值，然后选择作为新查询添加选项。 在此示例中选择了列表中的第一个文件，此文件恰好是 April 2019.csv 文件。

有关如何选择要作为示例文件的文件的屏幕截图。

此选项实际上会以二进制文件形式创建一个包含直接导航到该文件的导航步骤的新查询，此新查询的名称就是所选文件的文件路径。 将此查询重命名为 示例文件。

显示新的“示例文件”查询的屏幕截图。

使用名称“文件参数”和类型“二进制”创建新参数。 将“示例文件”查询作为“默认值”和“当前值”。

填充了文件参数值的屏幕截图。

注意

建议阅读关于参数的文章，以更好地了解如何在 Power Query 中创建和管理参数。

可以使用任何参数类型来创建自定义函数。 任何自定义函数都不需要将二进制文件作为参数。

只有当具有计算结果为二进制的查询时，二进制参数类型才会显示在参数对话框类型下拉菜单中。

无需参数即可创建自定义函数。 这在可以从调用函数的环境推断输入的情况下很常见。 例如，一个函数获取环境的当前日期和时间，并根据这些值创建特定的文本字符串。

右键单击查询窗格中的文件参数。 选择参考选项。

选择了“文件参数”的“引用”选项的屏幕截图。

将新创建的查询从文件参数 (2) 重命名为转换示例文件。

重命名后的“转换示例文件”查询的屏幕截图。

右键单击此新转换示例文件查询，然后选择创建函数选项。

用于“转换示例文件”查询的“创建函数”选项屏幕截图。

此操作实际上会创建一个与“转换示例文件”查询关联的新函数。 对“转换示例文件”查询所做的任何更改都会自动复制到该自定义函数。 在创建此新函数的过程中，请使用转换文件作为函数名称。

“转换文件”的“创建函数”窗口屏幕截图。

创建该函数后，你会发现系统使用函数的名称为你创建了一个新组。 该新组包含如下内容：

  • 转换示例文件查询中引用的所有参数。
  • 转换示例文件查询，通常称为示例查询。
  • 新创建的函数，在本例中为转换文件。

“查询”窗格中的函数组屏幕截图。

将转换应用于示例查询

在创建新函数后，请选择名称为转换示例文件的查询。 此查询现在与“转换文件”函数关联，因此，对此查询所做的任何更改都将反映到该函数。 这种关联就是所谓将示例查询关联到函数的概念。

需要对该查询进行的第一个转换是解释二进制文件的转换。 你可以从预览窗格中右键单击二进制文件，然后选择 CSV 选项将二进制文件解释为 CSV 文件。

突出显示 CSV 的二进制文件下拉菜单屏幕截图。

文件夹中所有 CSV 文件的格式都相同。 它们都有一个跨越前四行的标题。 列标题位于第 5 行，数据从第 6 行向下开始，如下图所示。

处理前的示例 CSV 数据屏幕截图。

需要应用于转换示例文件的下一组转换步骤如下：

  1. 移除前四行 - 此操作将删除被视为文件标题部分的行。

    从“转换示例”文件中移除前几行后的屏幕截图。

    注意

    若要详细了解如何删除行或按行位置筛选表，请转到按行位置筛选。

  2. 提升标题 - 最终表的标题现在位于表的第一行中。 你可以提供它们，如下图所示。

    “将第一行用作标题”的屏幕截图。

在提升列标题后，默认情况下 Power Query 将自动添加一个新的“更改类型”步骤，该步骤将自动检测每个列的数据类型。 “转换示例文件”查询与下图类似。

注意

若要详细了解如何提升和降级标题，请转到提升或降级列标题。

最终“转换示例”查询的屏幕截图。

注意

转换文件函数依赖于转换示例文件查询中执行的步骤。 但是，如果尝试手动修改转换文件函数的代码，则会收到一条警告，表明 The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

将自定义函数作为新列调用

创建了自定义函数并合并了所有转换步骤后，你可以返回到原始查询，其中包含文件夹中的文件列表（此示例中为“CSV 文件”）。 在功能区内的添加列选项卡中，从常规组中选择调用自定义函数。 在 调用自定义函数窗口中，输入输出表作为新列名称。 从函数查询下拉列表中选择函数的名称转换文件。 从下拉菜单中选择函数后，将显示函数的参数，你可以从表中选择要用作此函数的参数的列。 选择内容列作为要为文件参数传递的值/参数。

突出显示“调用自定义函数”按钮并设置了“调用自定义函数”对话框集中的设置的屏幕截图。

选择“确定”后，将创建一个名为“输出表”的新列。 此列在其单元格中具有表值，如下图所示。 为简单起见，请从此表中删除除名称和输出表之外的所有列。

调用自定义函数的屏幕截图，其中仅剩余“名称”和“输出表”列。

注意

若要详细了解如何从表中选择或删除列，请转到选择或删除列。

你的函数会应用于表中的每一行，并将内容列中的值作为函数的参数。 现在，数据已转换为需要的形状，可以通过选择“展开”图标来展开“输出表”列。 不要对展开后的列使用任何前缀。

展开后的输出表对话框屏幕截图，其中选择了所有表列。

你可以通过检查名称或日期列中的值来验证你是否具有文件夹内所有文件中的数据。 对于本例，你可以检查日期列中的值，因为每个文件只包含指定年份中单个月的数据。 如果看到多个值，则表示已成功将多个文件中的数据合并到单个表中。

验证最终表中是否包含来自所有文件的数据下拉列表屏幕截图。

注意

到目前为止，你阅读的内容基本上是合并文件体验期间发生的相同过程，但此过程是手动完成的。

我们还建议阅读有关合并文件概述和合并 CSV 文件的文章，以进一步了解合并文件体验在 Power Query 中的工作原理以及自定义函数的作用。

将新参数添加到现有自定义函数中

假设除当前所构建的内容外，还有新的要求。 由于这一新要求，需要在合并文件之前筛选这些文件中的数据，以便仅获取“国家/地区”为“巴拿马”的行。

若要实现此要求，请使用文本数据类型创建一个名为市场的新参数。 对于当前值，请输入值巴拿马。

具有必填值的新参数屏幕截图。

使用此新参数，选择转换示例文件查询，并使用市场参数中的值筛选国家/地区字段。

“筛选行”对话框的屏幕截图，其中“筛选国家/地区”列使用了新的“市场”参数。

注意

若要详细了解如何按值筛选列，请转到筛选值。

如果将此新步骤应用于查询，则将自动更新“转换文件”函数，该函数现在需要两个参数，而这两个参数基于“转换示例文件”所使用的两个参数。

现已使用两个参数更新函数的屏幕截图。

但 CSV 文件查询旁边有一个警告符号。 现在函数已更新，它需要两个参数。 因此，调用函数的步骤会产生错误值，因为在调用的自定义函数步骤中，只有其中一个参数传递给了转换文件函数。

函数更新后显示错误消息的屏幕截图。

若要修复错误，请在应用的步骤中双击调用的自定义函数以打开调用的自定义函数窗口。 在市场参数中，手动输入值巴拿马。

更新后的已调用自定义函数参数的屏幕截图。

现在可以返回到“已应用的步骤”中的“展开的输出表”。 检查查询，以验证“CSV 文件”查询的最终结果集中是否仅显示“国家/地区”为“巴拿马”的行。

更新参数后的最终输出表的屏幕截图。

利用可重用的逻辑片段创建自定义函数

如果有多个查询或值需要同一组转换，则可以创建一个充当可重用逻辑的自定义函数。 稍后，可以针对所选的查询或值调用此自定义函数。 此自定义函数可以节省时间，并帮助你在中心位置管理转换集，并随时可对其进行修改。

例如，假设一个查询包含多个代码作为文本字符串，并且你想要创建一个函数来解码这些值，如以下示例表中所示：

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

原始代码列表的屏幕截图。

首先创建一个参数，该参数具有一个作为示例的值。 在此例中，它是值“PTY-CM1090-LAX”。

“管理参数”对话框的屏幕截图，其中输入了示例参数代码值。

可以利用该参数创建一个可在其中应用所需转换的新查询。 对于这种情况，需要将代码 PTY-CM1090-LAX 拆分为多个组成部分：

  • 始发地 = PTY
  • 目的地 = LAX
  • 航空公司 = CM
  • FlightID = 1090

“示例转换”查询的屏幕截图，其中每个部分都位于自己的列中。

以下 M 代码演示了该转换集。

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

注意

若要了解关于 Power Query M 公式语言的详细信息，请转到 Power Query M 公式语言。

然后，你可以右键单击查询并选择创建函数，将该查询转换为函数。 最后，你可以将自定义函数调用到任何查询或值中，如下图所示。

代码列表的屏幕截图，其中填充了“调用自定义函数”值。

在进行更多的转换后，可以看到已产生所需的输出，并且应用了从自定义函数进行此类转换的逻辑。

调用自定义函数后的最终输出查询的屏幕截图。