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.
I have data that looks like this:
Fruit Type
Row 1. Apple=30% | Banana=20% | Orange=50%
Row 2. Apple=100%
Row 3. Banana=20% | Apple=70%
and I would like it to look like this:
Apple Banana Orange
Row 1. 30% 20% 50%
Row 2. 100% 0% 0%
Row 3. 70% 20% 0%
Any advice would be appreciated.
Solved! Go to Solution.
HI @Crow2525
Try this
Please see attached excel file's Query Editor for steps (with your sample data) as well
let Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit Type", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Fruit Type", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fruit Type.1", "Fruit Type.2", "Fruit Type.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit Type.1", type text}, {"Fruit Type.2", type text}, {"Fruit Type.3", type text}}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Percentage.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Value.1]), "Value.1", "Value.2") in #"Pivoted Column"
HI @Crow2525
Try this
Please see attached excel file's Query Editor for steps (with your sample data) as well
let Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit Type", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Fruit Type", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fruit Type.1", "Fruit Type.2", "Fruit Type.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Fruit Type.1", type text}, {"Fruit Type.2", type text}, {"Fruit Type.3", type text}}), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute"}), #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Percentage.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Value.1]), "Value.1", "Value.2") in #"Pivoted Column"
Thanks, I've applied your solution to my data with success, which had substantially more types of 'fruit'.
Appreciate your time and the provided excel file.
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |