Power Query M动态透视n行：多值列自动生成新行实现方案

问题背景 #

需要将输入数据转换为结构化透视输出，但待透视字段存在一对多值（如测试数据中的D、K字段对应多个值）时，直接调用Table.Pivot会触发报错。

直接透视报错效果：

初始复现代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2")
in
    #"Pivoted Column"

此前尝试添加全局索引列保证值唯一，但会引发行错位等其他问题；后续改用分组后文本合并再透视的方案，效果如下：

对应代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Column1"}, {{"Column2", each Text.Combine([Column2],"#(lf)"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Column1]), "Column1", "Column2")
in
    #"Pivoted Column"

该方案存在明显弊端：透视后需要逐列拆分合并的文本，且无法适配列数动态变化的场景（列范围从A-B到A-AZ不等时，需要动态配置拆分逻辑，复杂度高、灵活性差）。

测试输入数据 #

Column1 Column2
A   1
B   1
C   2
D   3
D   3
D   1
E   2
F   1
G   2
H   1
I   2
J   3
K   1
K   2
L   1
M   2
N   3

实现方法 #

通过给每个字段分组内的重复值添加序号索引，再基于索引完成透视，全程不需要做文本合并/拆分，自动适配任意数量的待透视列，多值自动生成新行。

可用M代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 按实际业务需要配置列类型即可
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    // 按待透视字段分组，给每组内的值添加从1开始的行序号，解决多值重复冲突
    #"Grouped with Index" = Table.Group(#"Changed Type", {"Column1"}, {
        {"GroupData", each Table.AddIndexColumn(_, "RowNo", 1, 1), type table}
    }),
    // 展开分组表
    #"Expanded GroupData" = Table.ExpandTableColumn(#"Grouped with Index", "GroupData", {"Column2", "RowNo"}, {"Column2", "RowNo"}),
    // 动态透视，自动识别所有待透视列，无需硬编码列名
    #"Pivoted" = Table.Pivot(#"Expanded GroupData", List.Distinct(#"Expanded GroupData"[Column1]), "Column1", "Column2"),
    // 按行号排序后删除辅助索引列
    #"Sorted by RowNo" = Table.Sort(#"Pivoted", {{"RowNo", Order.Ascending}}),
    #"Removed Aux Column" = Table.RemoveColumns(#"Sorted by RowNo", {"RowNo"}),
    // 清除透视生成的全空冗余行，得到最终结果
    #"Cleaned Empty Rows" = Table.SelectRows(#"Removed Aux Column", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {null, ""})))
in
    #"Cleaned Empty Rows"

方案特性 #

• 全程保持字段原有数据类型，不需要做文本合并、后续拆分操作
• 自动适配任意列数的透视场景，新增/减少待透视字段不需要修改代码逻辑
• 重复值自动按原始顺序换行对齐，不会出现行错位问题，输出结果和目标效果完全匹配

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