Forum Discussion
Solved
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 ...
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"
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.