Create an advanced pivot table

[HairyBoatmen]

I'd like to create a pivot table where column names and data values in the original data table become row headings and column headings in the pivot table:

 

[alansidman]

A power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Attribute - Copy", List.Count),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "sm", "med", "lg"})
in
    #"Reordered Columns"

Book3
	A	B	C	D	E	F	G	H	I	J
1	ID	Eyes	Nose	Ears	Hands		Attribute	sm	med	lg
2	a	med	lg	med	sm		Ears	3	2	3
3	b	med	sm	lg	med		Eyes	3	3	2
4	c	lg	med	sm	med		Hands	1	5	2
5	d	sm	lg	sm	lg		Nose	3	2	3
6	e	sm	sm	med	med
7	f	sm	med	lg	med
8	g	med	sm	lg	med
9	h	lg	lg	sm	lg
Sheet1

 

[HairyBoatmen]

That' s brilliant. Thanks so much Alan.

Is there no way to do this with normal pivot functionality? I'd prefer to have the ability to slice and drill down as I would with a more basic pivot table

 

[alansidman]

Once you unpivot the data in Power Query, then close and load it to a Pivot Table in native excel. Either way you need to have the underlying data in a normalized manner in order to Pivot the information.