Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |