Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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"
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.
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |