PowerBI - unpivot从列中选择的值

Question

我有一个表，其中有Id和类别的列表。见下文:

<表类> id 类别价值tbody><<tr>1参照1地区MTO1参照1区MT991参照1站ST1241参照2地区MTO1参照2区MT851参照2站ST4201参照3地区BSW正在1"三个区域BS881参照3站ST8762参照1地区td>2参照1区GT342参照1站STT5552参照2地区MTO2参照2区MT992参照2站ST1242参照3地区BSW正在2"三个区域BS882参照3站ST876

这将产生第二个表:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Category", type text}, {"Value", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Category", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Category.1", "Category.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Category.2]), "Category.2", "Value"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"id", "Category.1", "Region", "Area"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Category.1", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Category"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"id", "Category", "Value", "Region", "Area"})
in
    #"Reordered Columns"