Help pivoting/structuring data from old legacy system

[drdataguy]

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.

[Ashish_Mathur]

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.

[Greg_Deckler · Accepted solution]

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"

[drdataguy]

That worked amazingly. Thanks for your help! 

[Greg_Deckler]

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.