How to transpose this table in Excel? Columns should be called with the Names (Sophia, Anna) that represent the group and Years that represent the subgroup. Rows should be called like the fruits they represent. The fact that I have a group and a subgroup makes it problematic.
So the table looks like this:
2007 | 2008 | ||
---|---|---|---|
Sophia | Avocado | 20 | 30 |
Sophia | Blueberry | 100 | 300 |
Sophia | Lemon | 50 | 70 |
Sophia | Strawberry | 200 | 400 |
Anna | Blueberry | 400 | 500 |
Anna | Strawberry | 100 | 200 |
Anna | Orange | 40 | 50 |
Anna | Apple | 50 | 70 |
And should look like this:
Sophia | Anna | |||
---|---|---|---|---|
2007 | 2008 | 2007 | 2008 | |
Blueberry | 100 | 300 | 400 | 500 |
Strawberry | 200 | 400 | 100 | 200 |
Other | 70 | 100 | 90 | 120 |
In powerquery, start off by highlighting the data and using data .. from table/range [x] titles. Paste code below into home....advanced editor.... and then load it back into excel with file ... close and load....
(if home...advanced editor... shows different code for first row, use that first row in place of first row below)
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Column1]& " "&Text.From([Attribute])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", each if Text.Contains([Column2],"berry") then [Column2] else "Other"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1", "Attribute", "Column2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in #"Pivoted Column"