通过

使用自定义函数

  • 项目

如果你发现自己在需要将同一组转换应用到不同的查询或值的情况下，请创建一个可根据需要重复使用的 Power Query 自定义函数，该函数可以根据需要多次重复使用。 Power Query 自定义函数是从一组输入值映射到单个输出值，是从本机 M 函数和运算符创建的。

可以使用 Power Query M 公式语言手动创建自己的 Power Query 自定义函数，或者 Power Query 用户界面提供了加快、简化和增强创建自定义函数创建和管理过程的功能。

首先，我们将介绍 使用 UI中的代码创建自定义函数的基本步骤，然后我们将重点介绍如何使用接口 将复杂作转换为可重用函数。

重要

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

从 UI 中的代码创建自定义函数

注意

可以在 Power BI Desktop 中或使用在 Excel for Windows 中找到的 Power Query 体验执行以下步骤。

  1. 使用连接器体验连接到数据所在的位置。 当您选择了数据后，选择 “转换数据”或“编辑”按钮。 这会将你带到 Power Query 体验。
  2. 右键单击左侧“查询”窗格中的空白处。
  3. 选择“空白查询”。
  4. 在新空白查询窗口中，选择 主页 菜单，然后 高级编辑器。
  5. 将模板替换为自定义函数。 例如：
    let
 HelloWorld = () => ("Hello World")
in
 HelloWorld
  6. 选择“完成”。

有关使用 Power Query M 公式语言开发自定义函数的详细信息，请参阅本文：了解 Power Query M 函数。 在以下部分中，有一些教程介绍了如何使用 Power Query 用户界面在不编写代码的情况下开发自定义函数，以及有关如何在查询中调用自定义函数的说明。

从表引用教程创建自定义函数

注意

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

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

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

如何选择文件以用作示例文件的截图。

此选项有效地创建一个新查询，其中包含一个导航步骤，将文件直接作为二进制对象访问，新查询的名称是所选文件的文件路径。 将此查询重命名为 示例文件。

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

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

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

注意

建议阅读有关 Parameters 的文章，以便更好地了解如何在 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 文件）。 在功能区的“添加列”选项卡中，从“常规”组中选择“调用自定义函数”。 在 调用自定义函数 窗口中，将 输出表 输入为 新列名。 从“函数查询”下拉菜单中选择函数名称“转换文件”。 从下拉菜单中选择函数后，将显示函数的参数，可以从表中选择要用作此函数的参数的列。 选择 内容 列作为要为 文件参数传递的值/参数。

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

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

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

注意

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

使用来自 Content 列的值作为参数，您的函数被应用于表中的每一行。 现在，数据已转换为要查找的形状，可以通过选择“展开”图标来展开 输出表 列。 不要对展开列使用任何前缀。

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

可以通过检查 名称 或 Date 列中的值来验证文件夹中的所有文件中的数据。 在这种情况下，可以检查 日期 列中的值，因为每个文件仅包含给定年份中单个月的数据。 如果看到多个文件，则表示已成功将多个文件中的数据合并到单个表中。

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

注意

到目前为止，你所阅读的内容与 合并文件 操作期间本质上是相同的过程，只不过是手动完成的。

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

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

假设在当前构建的内容的基础上有一个新的要求。 新要求要求在合并文件之前筛选其中的数据，以便仅获取“国家/地区”等于“巴拿马”的行。

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

新参数及其所需值的屏幕截图。

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

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

注意

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

将此新步骤应用于查询会自动更新 转换文件 函数，该函数现在需要两个参数，具体取决于 转换示例文件 使用的两个参数。

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

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

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

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

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

现在可以返回到 应用步骤中的 扩展输出表。 请检查您的查询以确认只有 国家/地区 等于 巴拿马 的行出现在 CSV 文件 查询的最终结果集中。

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

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

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

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

代码
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

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

首先，有一个参数，该参数具有一个用作示例的值。 在本例中，它的值是 PTY-CM1090-LAX。

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

根据该参数创建一个新查询，并在其中应用所需的转换。 在这种情况下，你需要将代码 PTY-CM1090-LAX 拆分为多个组件。

  • 原点 = PTY
  • 目标 = LAX
  • 航空公司 = CM
  • 航班ID = 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 公式语言。

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

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

再进行一些转换后，可以看到你达到了所需的输出，并应用了从自定义函数进行此类转换的逻辑。

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