如何用Power Query重构含多表头的Excel表，将表头转为横向新列

我是Power Query新手，正在处理格式混乱的投资账户数据清洗，目前完成了部分步骤但遇到瓶颈。数据包含不同证券类型信息，每种类型首行是该类别专属表头，各类别表头不同，需要将这些表头映射到统一的属性列中。我已经基于最后一列的Category字段分组生成了表列，但不知道如何把Attribute 1、2等属性整理到对应列。

现有M代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="tbldata"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column7", "Catagory"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Catagory"}, {{"Grouped", each _, type table [Column1=text, Column2=any, Column3=any, Column4=any, Column5=any, Column6=any, Catagory=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom1", each Table.PromoteHeaders([Grouped])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"})

in
    #"Removed Columns"

原始数据表格 #

期望重构后表格 #

解决方案M代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="tbldata"]}[Content],
    // 重命名最后一列为Category
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column7", "Category"}}),
    // 按Category分组
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Category"}, {{"GroupedData", each _, type table [Column1=text, Column2=any, Column3=any, Column4=any, Column5=any, Column6=any, Category=text]}}),
    // 处理每个分组：提取表头行，构建映射，转换数据行
    #"Processed Groups" = Table.AddColumn(#"Grouped Rows", "Processed", (group) =>
        let
            groupTable = group[GroupedData],
            // 提取第一行作为当前类别的表头映射
            headerRow = Table.FirstN(groupTable, 1),
            // 提取数据行（从第二行开始）
            dataRows = Table.Skip(groupTable, 1),
            // 将表头行转成列表，构建原始列名到目标属性的映射
            headerMap = List.Zip({Table.ColumnNames(headerRow), Table.ToList(headerRow{0})}),
            // 重命名数据行的列，用映射替换原始列名
            #"Renamed Data Columns" = Table.RenameColumns(dataRows, headerMap),
            // 添加对应类型的名称列（比如Stock对应Code，Fund对应Fund name等）
            #"Added Type Column" = Table.RenameColumns(#"Renamed Data Columns", {{Table.ColumnNames(#"Renamed Data Columns"){0}, group[Category] & " name"}})
        in
            #"Added Type Column"),
    // 移除原始分组数据列
    #"Removed Grouped Column" = Table.RemoveColumns(#"Processed Groups", {"GroupedData"}),
    // 展开所有处理后的表，自动补全缺失列的空值
    #"Expanded Processed" = Table.ExpandTableColumn(#"Removed Grouped Column", "Processed", 
        List.Distinct(List.Combine(Table.Column(#"Removed Grouped Column", "Processed") |> List.Transform(Table.ColumnNames)))),
    // 重命名Stock name列为Code
    #"Renamed Stock Column" = Table.RenameColumns(#"Expanded Processed", {{"Stock name", "Code"}}),
    // 调整列顺序，匹配期望结果
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Stock Column", {"Code", "Fund name", "Bond name", "Attribute 1", "Attribute 2", "Attribute 3", "Attribute 4", "Attribute 5", "Attribute 6", "Attribute 7", "Attribute 8", "Attribute 9", "Attribute 10"})
in
    #"Reordered Columns"

代码核心逻辑 #

1. 分组处理：按Category拆分数据，每个分组单独处理专属表头和数据
2. 表头映射：提取每个分组的首行作为映射规则，将原始列名替换为实际属性名称
3. 类型列匹配：将每个分组的第一列重命名为对应类型的名称列（如Fund分组的第一列改为"Fund name"）
4. 合并展开：展开所有分组结果，Power Query会自动为缺失列填充空值
5. 列序调整：最后调整列顺序与期望输出一致

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