答案1
这是在 PowerQuery 中执行此操作的一种方法。
将 id 和 total 移到左侧,选择 id 和 total,然后使用 Transform>Unpivot Other Columns 得到以下结果:
选择“值”列,右键单击并使用“按分隔符拆分列”,配置如下:
你现在有这个:
选择 id、total 和 Attribute,然后使用 Transform>Unpivot Other Columns。现在您应该看到以下内容:
选择属性并使用变换>枢轴列,配置如下:
你现在应该有这个:
根据需要删除/重命名/重新定位列,然后使用 Home>Close & Load 将数据放回工作簿。我已重命名并更改了 Attribute.1 列以显示每个组内的索引(以后可能会有用!):
我的源数据在名为 Table1 的表中。以下是高级编辑器中的全文:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"quantity", type text}, {"total", type number}, {"item price", type text}, {"article number", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"id", "total", "quantity", "item price", "article number"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"id", "total"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type number}, {"Value.2", type number}, {"Value.3", type number}}),
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"id", "total", "Attribute"}, "Attribute.1", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns1", List.Distinct(#"Unpivoted Other Columns1"[Attribute]), "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "index_in_id"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Value.","",Replacer.ReplaceText,{"index_in_id"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"index_in_id", Int64.Type}})
in
#"Changed Type2"