Forum Discussion
Help pivoting/structuring data from old legacy system
Hi,
I'm pulling some product information our of an old legacy system which structures the data in a horrible way. The list of products for a customer is in a single multi-select field. Here's an example of how the data is structured:
| User Code | Products |
| 1 | apple; banana; grape; pineapple |
| 2 | banana; pear; strawberry |
| 3 | apple; grape |
| 4 | strawberry |
| 5 | peach; pear |
I've split the columns in PowerBI but as the original values are alphabetical, the data gets all messed up like so:
| User Code | Product1 | Product2 | Product3 | Product4 | Product5 |
| 1 | apple | banana | grape | pineapple | |
| 2 | banana | pear | strawberry |
The end result I am looking for is something like this:
| User Code | Apple | Banana | Grape | Pear | Peach | Pinapple | Strawberry |
| 1 | Yes | Yes | Yes | Yes | |||
| 2 | Yes | Yes | Yes | ||||
| 3 | Yes | Yes |
Any ideas on how I can acheive this? Open to any suggestions.
Thanks.
Try something like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUosKMhJtVZISswDQmuF9KLEAiC3IDMvFSyjFKsTrWQEVAdTUJCaWGStUFxSlFielFpUVAlWYIwwCGwAWNAEKIimzhQoBDQgOQNijlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User Code" = _t, Products = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Code", Int64.Type}, {"Products", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Products", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Products.1", "Products.2", "Products.3", "Products.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}, {"Products.3", type text}, {"Products.4", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"User Code"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count) in #"Pivoted Column"
4 Replies
Try something like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUosKMhJtVZISswDQmuF9KLEAiC3IDMvFSyjFKsTrWQEVAdTUJCaWGStUFxSlFielFpUVAlWYIwwCGwAWNAEKIimzhQoBDQgOQNijlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User Code" = _t, Products = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Code", Int64.Type}, {"Products", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Products", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Products.1", "Products.2", "Products.3", "Products.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products.1", type text}, {"Products.2", type text}, {"Products.3", type text}, {"Products.4", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"User Code"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Value]), "Value", "Attribute", List.Count) in #"Pivoted Column"That worked amazingly. Thanks for your help!
Glad to help! I'm trying to get better at my Power Query skills versus always relying on ImkeF. So I like when I can solve these. But if you are ever really in a bind and need real Power Query expertise, just mention ImkeF in your post. She is amazing with Power Query and in my opinion one of the best, if not THE best.
Hi,
This M code works as well
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Code", Int64.Type}, {"Products", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Products", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Products"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Products", Text.Trim, type text}}), #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Products", Order.Ascending}, {"User Code", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Response", each "Yes"), #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Products]), "Products", "Response") in #"Pivoted Column"Hope this helps.
