Forum Discussion
Solved
Transposing a table of data
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!
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.
4 Replies
- Greg_Deckler
Community Champion
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"Thank you so much for your help!
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.
Thank you so much for your help!
