使用Power Query实现表格逆透视转换的方法求助

需求说明 #

原始表格包含ID字段，以及6组Type N/Count N类型的字段（示例中展示3组），需要将其转换为按Type分组，统计对应Count总和的表格，同时处理原始数据中的空值、非数值脏数据。

具体操作步骤 #

1. 导入数据到Power Query #

将Excel表格数据导入Power Query（数据选项卡→从表格/范围）。

2. 逆透视非ID列 #

• 选中ID列，点击「转换」选项卡 → 「逆透视列」→ 「逆透视其他列」
• 操作后将生成Attribute（存储Type 1/Count 1等字段名）和Value（对应字段值）两列。

3. 拆分字段名列 #

• 选中Attribute列，点击「转换」→ 「拆分列」→ 「按分隔符」，选择空格作为分隔符，拆分为两列，分别命名为字段类型（对应Type/Count）和组序号。

4. 透视列匹配Type与Count #

• 选中组序号列，点击「转换」→ 「透视列」：
  • 值列选择Value
  • 列名选择字段类型
• 操作后将得到ID、组序号、Type、Count四列，每组Type/Count对应一行。

5. 清洗Count列数据 #

• 选中Count列，点击「转换」→ 「数据类型」→ 「整数」，将非数值内容转换为错误值；
• 点击「转换」→ 「替换值」，将错误值和空值替换为0（确保求和时不会出错）。

6. 分组统计Type的总Count #

• 选中Type列，点击「转换」→ 「分组依据」：
  • 分组依据选择Type
  • 新列名设为总Count
  • 操作选择「求和」，列选择Count
• 点击确定后即可得到按Type聚合的统计结果。

7. 导出数据 #

点击「关闭并上载」，将转换后的数据导出到Excel工作表。

快速实现的M代码 #

如果需要批量处理或自动化操作，可以直接将以下代码粘贴到Power Query的「高级编辑器」中（替换原有的代码）：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视其他列 = Table.UnpivotOtherColumns(源, {"ID"}, "Attribute", "Value"),
    拆分列按分隔符 = Table.SplitColumn(逆透视其他列, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"字段类型", "组序号"}),
    透视列 = Table.Pivot(拆分列按分隔符, List.Distinct(拆分列按分隔符[字段类型]), "字段类型", "Value"),
    更改类型 = Table.TransformColumnTypes(透视列,{{"Count", Int64.Type}}),
    替换错误值 = Table.ReplaceErrorValues(更改类型, {{"Count", 0}}),
    替换空值 = Table.ReplaceValue(替换错误值,null,0,Replacer.ReplaceValue,{"Count"}),
    分组依据 = Table.Group(替换空值, {"Type"}, {{"总Count", each List.Sum([Count]), Int64.Type}})
in
    分组依据

注意：代码中的表1需要替换为你实际的表格名称。

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