数据透视表Values区域能否显示文本？Power Query多值报错求助

问题背景 #

• 数据源包含Function、Item、Area（仅app或sp）、Updated Part字段，需要转换为透视格式，但Excel原生数据透视表无法直接显示文本值
• 使用Power Query代码透视时，当同一Item对应多个Area和Updated Part，出现报错：

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
    [List]

• 使用的原代码：

let
//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//set all the column data types to type text
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Function] <> null and [Function] <> ""),
//Pivot the "Area" column with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Area]), "Area", "Updated Part"),
//to blank repeated functions:
    //add shifted column
    #"Add Shifted function" = Table.FromColumns(
        Table.ToColumns(#"Pivoted Column")
        & {{null} & List.RemoveLastN(#"Pivoted Column"[Function])},
        Table.ColumnNames(#"Pivoted Column") & {"Shifted Function"}),
    //Blank if shifted <> function
    #"Null Repeats" = Table.ReplaceValue(
        #"Add Shifted function",
        each [Function],
        each [Shifted Function],
        (x,y,z) as nullable text => if y <> z then y else null,
        {"Function"}),
    #"Removed Columns" = Table.RemoveColumns(#"Null Repeats",{"Shifted Function"})
in
    #"Removed Columns"

问题原因与解决方法 #

原因 #

原代码直接用Table.Pivot但未指定聚合逻辑，当同一Function+Item分组下，同一Area对应多个Updated Part时，Power Query无法确定要显示哪个值，导致报错。这不是方法不支持多值，而是需要明确多值的处理规则。

解决思路 #

通过自定义聚合函数，把同一分组下的多个Updated Part合并为单个文本（比如用分隔符拼接），再执行透视操作，同时保留原有的重复Function置空逻辑。

修改后的代码 #

let
    // 数据源
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 统一设置为文本类型
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    // 过滤空Function行
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Function] <> null and [Function] <> ""),
    // 关键：按Function+Item+Area分组，合并同一Area下的多个Updated Part
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Function", "Item", "Area"}, {
        {"Combined Updated Parts", each Text.Combine([Updated Part], ", "), type text}
    }),
    // 透视Area列，使用合并后的文本值
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Area]), "Area", "Combined Updated Parts"),
    // 处理重复Function置空
    #"Add Shifted Function" = Table.FromColumns(
        Table.ToColumns(#"Pivoted Column")
        & {{null} & List.RemoveLastN(#"Pivoted Column"[Function])},
        Table.ColumnNames(#"Pivoted Column") & {"Shifted Function"}),
    #"Null Repeats" = Table.ReplaceValue(
        #"Add Shifted Function",
        each [Function],
        each [Shifted Function],
        (x,y,z) as nullable text => if y <> z then y else null,
        {"Function"}),
    #"Removed Columns" = Table.RemoveColumns(#"Null Repeats", {"Shifted Function"})
in
    #"Removed Columns"

说明 #

• 分组时用Text.Combine([Updated Part], ", ")将同一Area下的多个更新项用逗号拼接，可根据需求修改分隔符（比如换行符#(lf)）
• 如果需要保留多值的列表形式，可将聚合函数改为each [Updated Part]，但透视后单元格会显示列表，需手动展开或调整显示方式

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