先逆透视后9列,然后把“属性”分列成地区和描述列,然后再透视描述列- let
Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
#"Replaced Errors" = Table.ReplaceErrorValues(Source, List.Transform(Table.ColumnNames(Source), each {_,null})),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Errors", {"货号", "货名"}, "属性", "值"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "属性", Splitter.SplitTextByPositions({0, 2}, false), {"地区", "描述"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Position", List.Distinct(#"Split Column by Position"[描述]), "描述", "值", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"地区", Order.Ascending}})
in
#"Sorted Rows"
复制代码 |