Excel: Transposing the table when having group and subgroup

Question

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

Answer 1

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"