如何在Power Query中格式化含双表头的Excel表格

核心思路 #

原表格是复合表头（前两行包含类别、年月信息，第三行是基础字段名），需要先提取类别标识，再通过逆透视将年月列转为行，最后拆分年月、整理列顺序得到目标结构。

可视化操作步骤 #

1. 导入数据到Power Query
   • 从Excel或对应数据源导入表格，此时会看到前两行是表头元信息，第三行是字段名，第四行开始为数据。
2. 提取类别信息
   • 记住第一行D列的类别名称（如Category 1），后续会用到。
3. 清理表头
   • 删除第一行，然后选中第二行，点击转换>提升为标题，将第二行设置为表格的正式表头。
4. 逆透视年月列
   • 选中前3列（Parent/ Child Name、ID、Parent/ Child），右键选择逆透视其他列，生成Attribute（年月）和Value（数值）两列。
5. 添加类别列
   • 点击添加列>自定义列，输入类别名称（如"Category 1"），将列命名为Category。
6. 拆分年月
   • 选中Attribute列，点击转换>拆分列>按分隔符，选择空格作为分隔符，拆分为Month和Year两列。
7. 整理列顺序
   • 拖动列调整为：Parent/ Child Name、ID、Category、Parent/ Child、Year、Month、Value，删除多余列即可。

一键式M代码（替换高级编辑器内容） #

直接复制以下代码到Power Query的高级编辑器中，替换Source行的数据源为你的实际表名：

let
    // 替换为你的数据源，示例为当前工作簿中的Table1
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 提取第一行的类别（D列，索引从0开始对应第4列）
    Category = Source{0}[Column4],
    // 删除第一行
    RemoveFirstRow = Table.Skip(Source, 1),
    // 将第二行设为表头
    PromoteHeaders = Table.PromoteHeaders(RemoveFirstRow, [PromoteAllScalars=true]),
    // 逆透视非基础字段列
    UnpivotOtherColumns = Table.UnpivotOtherColumns(PromoteHeaders, {"Parent/ Child Name", "ID", "Parent/ Child"}, "Attribute", "Value"),
    // 添加类别列
    AddCategory = Table.AddColumn(UnpivotOtherColumns, "Category", each Category),
    // 拆分年月字段
    SplitDate = Table.SplitColumn(AddCategory, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Month", "Year"}),
    // 调整列顺序+转换数据类型
    FinalTable = Table.ReorderColumns(Table.TransformColumnTypes(SplitDate,{{"Year", Int64.Type}, {"Value", Int64.Type}}), 
                                     {"Parent/ Child Name", "ID", "Category", "Parent/ Child", "Year", "Month", "Value"})
in
    FinalTable

多类别场景扩展 #

如果表格包含多个类别组（如D-F列对应Category1，G-I对应Category2），需要先按类别分组识别列范围，循环处理每个类别后合并结果。可基于上述代码，添加列分组逻辑实现批量处理。

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