Power Query M语言自定义Group By函数报错求助

需求说明 #

• 需创建一个可复用的自定义函数，包含3个参数：
  • report：输入的数据表
  • field：指定要计算最大值的字段名
  • columnNewName：存储结果的新列名称
• 函数核心功能：按Appl Nbr列分组，计算指定字段的最大值，将结果存入自定义名称的新列，支持多数据表复用

报错代码片段 #

(report as table, field as text, columnNewName as text) =>

let
    Source = report,
    SelectedField = Table.SelectColumns(Source, field),
    #"Grouped Rows" = Table.Group(Source, {"Appl Nbr"}, {{columnNewName, each List.Max(SelectedField), type nullable number}})
in
    #"Grouped Rows"

报错原因：分组的each上下文无法直接引用外部的SelectedField表对象，且List.Max要求输入为列表而非表结构。

可正常运行的硬编码示例 #

(report as table, field as text, columnNewName as text) =>

let
    Source = report,
    #"Grouped Rows" = Table.Group(Source, {"Appl Nbr"}, {{columnNewName, each List.Max([No. of Days]), type nullable number}})
in
    #"Grouped Rows"

测试用数据表 #

Table1 #

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTI0BRKheZklqSkKwSWJJanFQL5vZnJGYmqOQnByfkmJUqwOQjWYdCtKzEtOxa8OSLinFuUm5lUCWT6lKeWZ6QpOqaklGfllqXlQpaZwpegOQOVDVJuDXGqMzbloqmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Appl Nbr" = _t, #"No. of Days" = _t, Country = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Appl Nbr", Int64.Type}, {"No. of Days", Int64.Type}, {"Country", type text}, {"Name", type text}})
in
    #"Changed Type"

Table2 #

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIC4tC8zJLUFIXgksSS1GIg3zczOSMxNUchODm/pEQpVgeu2BCI3YoS85JT8akyAWL31KLcxLxKIMunNKU8M13BKTW1JCO/LDVPKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Appl Nbr" = _t, #"No. of Weeks" = _t, Country = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Appl Nbr", Int64.Type}, {"No. of Weeks", Int64.Type}, {"Country", type text}, {"Name", type text}})
in
    #"Changed Type"

解决方法 #

方案1：使用Record.Field动态获取字段列表 #

(report as table, field as text, columnNewName as text) =>

let
    Source = report,
    #"Grouped Rows" = Table.Group(Source, {"Appl Nbr"}, {
        {columnNewName, each List.Max(Record.Field(_, field)), type nullable number}
    })
in
    #"Grouped Rows"

• 代码说明：Record.Field(_, field)在分组的行上下文（_代表当前分组的行集合）中，动态提取指定字段的所有值并返回列表，直接传入List.Max计算最大值。

方案2：使用Table.Column提取分组列 #

(report as table, field as text, columnNewName as text) =>

let
    Source = report,
    #"Grouped Rows" = Table.Group(Source, {"Appl Nbr"}, {
        {columnNewName, each List.Max(Table.Column(_, field)), type nullable number}
    })
in
    #"Grouped Rows"

• 代码说明：Table.Column(_, field)直接从当前分组的子表中提取指定字段的列数据，返回列表后计算最大值，逻辑更直观。

两种方案均保留了参数化的灵活性，可直接传入不同数据表、字段名和新列名实现复用。

内容的提问来源于stack exchange，提问作者maximodesousadias