Transposing a table of data

[theapengelly]

Hi, 

I have this table currently in PowerBI Desktop, where there are different types of item labels, all linked to each item by the Item column.

Label	Item
Colour- Blue	A
Colour- Purple	A
Material- Wool	A
Clothing- Jumper	A
Colour- Green	B
Material- Nylon	B
Clothing- Shorts	B
Material- Cotton	C
Clothing- Shirt	C

I need to be able to create a row per 'unique' set of Colour values like below. So as there are two Colour labels attributed to A, it duplicates the Material and Clothing values and creates two separate rows. 

Item	Colour	Material	Clothing
A	Blue	Wool	Jumper
A	Purple	Wool	Jumper
B	Green	Nylon	Shorts
C	null	Cotton	Shirt

Anyone have any idea how I do this? Especially as some of the items won't have every single label either. 

Thank you in advance!

[Greg_Deckler · Accepted solution]

@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"

[v-xinruzhu-msft · Accepted solution]

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.

[theapengelly]

Thank you so much for your help!

[Greg_Deckler · Accepted solution]

@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"

[theapengelly]

Thank you so much for your help!