let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
逆透视 = Table.UnpivotOtherColumns(源,{"Period","Staff Name"},"项目","值"),
分组 = Table.Group(逆透视,{"Staff Name","项目"},{"分组",each Table.RemoveColumns(Table.AddColumn(_,"合并项目",each [项目] & "/" &[Period]),{"Period","项目"})}),
分组透视 = Table.TransformColumns(分组,{"分组",each Table.Pivot(_,[合并项目],"合并项目","值",List.Sum)}),
二次分组 = Table.Group(分组透视,"Staff Name",{"二次分组",each Table.Combine([分组])}),
聚合列表 = List.Transform(List.Skip(List.Distinct(List.Combine(List.Transform(二次分组[二次分组],Table.ColumnNames)))),each {_,List.Sum,_}),
聚合 = Table.AggregateTableColumn(二次分组,"二次分组",聚合列表)
in
聚合
|