Transposing a table of data

theapengelly Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PyS8t0lVwyilNVdJRclSK1UEIBpQWFeQghH0TS1KLMhNzdBXC8/NzEKpz8ksyMvPSdRW8SnMLUoswjHEvSk3NA4o6oZniV5mTjxBHGBOckV9UUoxFg3N+SQlYhzOGjsyiEvzisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Item", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Label", Splitter.SplitTextByDelimiter("- ", QuoteStyle.Csv), {"Label.1", "Label.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Label.1", type text}, {"Label.2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Item", "Label.1"}, {{"Attributes", each _, type table [Label.1=nullable text, Label.2=nullable text, Item=nullable text]}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Label.1]), "Label.1", "Attributes"),
    #"Expanded Clothing" = Table.ExpandTableColumn(#"Pivoted Column", "Clothing", {"Label.2"}, {"Clothing.Label.2"}),
    #"Expanded Material" = Table.ExpandTableColumn(#"Expanded Clothing", "Material", {"Label.2"}, {"Material.Label.2"}),
    #"Expanded Colour" = Table.ExpandTableColumn(#"Expanded Material", "Colour", {"Label.2"}, {"Colour.Label.2"})
in
    #"Expanded Colour"

Hi,

Thanks for the solution Greg_Deckler offered, and i want to offer some more information for user to refetr to.

hello theapengelly , you can refer to the follwing solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcwxCoAwDIXhq5TO9hDaQRAUwcFBOjgELUQjMR28vaJDQF0/3vuHwXpCSuxMgQlsZnMbMsU28YbK9SjAcURneiLUNZLMcZ2cqdKyAX8yJQOslxavSnMgqWumm4ll/zl4Erkf/vOILI+HEw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Label = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Item", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Label", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Label.1", "Label.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Label.1", type text}, {"Label.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Label.2", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Label.1", "Item"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Label.2", "Index"}, {"Label.2", "Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US"),{"Item", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Merged", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Label.1]), "Label.1", "Label.2"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Material", "Clothing"})
in
    #"Filled Down"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.