培训
认证
Microsoft Certified: Power Platform Functional Consultant Associate - Certifications
作为功能顾问,你可以使用 Microsoft Power Platform 工具和组件进行发现、捕获需求、吸引主题专家和利益干系人以及配置业务解决方案。
如果你发现自己需要将同一组转换应用于不同的查询或值,那么创建一个可以根据需要多次重复使用的 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 文件的格式都相同。 它们都有一个跨越前四行的标题。 列标题位于第 5 行,数据从第 6 行向下开始,如下图所示。
需要应用于转换示例文件的下一组转换步骤如下:
移除前四行 - 此操作将删除被视为文件标题部分的行。
备注
若要详细了解如何删除行或按行位置筛选表,请转到按行位置筛选。
提升标题 - 最终表的标题现在位于表的第一行中。 你可以提供它们,如下图所示。
在提升列标题后,默认情况下 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 拆分为多个组成部分:
以下 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 公式语言。
然后,你可以右键单击查询并选择创建函数,将该查询转换为函数。 最后,你可以将自定义函数调用到任何查询或值中,如下图所示。
在进行更多的转换后,可以看到已产生所需的输出,并且应用了从自定义函数进行此类转换的逻辑。
培训
认证
Microsoft Certified: Power Platform Functional Consultant Associate - Certifications
作为功能顾问,你可以使用 Microsoft Power Platform 工具和组件进行发现、捕获需求、吸引主题专家和利益干系人以及配置业务解决方案。