How to pivot a data sheet to count multiple columns with Yes/No entries

[Highlander Mc]

Hi

I'm trying to pivot the following scenario:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Chairs[/TD]
[TD="width: 64"]Tables[/TD]
[TD="width: 64"]Carpets[/TD]
[/TR]
[TR]
[TD]Room 1[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Room 2[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Room 3[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To give the following answer
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]Yes[/TD]
[TD="class: xl65, width: 64"]No[/TD]
[TD="class: xl65, width: 64"]Total[/TD]
[/TR]
[TR]
[TD]Chairs[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD]Tables[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD]Carpets[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
</tbody>[/TABLE]

Currently my efforts just seem to stack the different Yes/No on top of each other. Is it possible to achieve the above?

 

[sandy666]

With PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Items[/td][td=bgcolor:#5B9BD5]Chairs[/td][td=bgcolor:#5B9BD5]Tables[/td][td=bgcolor:#5B9BD5]Carpets[/td][td][/td][td=bgcolor:#DDEBF7]Count of Items[/td][td=bgcolor:#DDEBF7]Value[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Room 1[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Yes[/td][td][/td][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Room 2[/td][td]No[/td][td]Yes[/td][td]Yes[/td][td][/td][td]Carpets[/td][td]

3​

[/td][td]

0​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Room 3[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]Yes[/td][td][/td][td]Chairs[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Tables[/td][td]

2​

[/td][td]

1​

[/td][td]

3​

[/td][/tr]
[/table]

step1
Load source table into PQ editor then

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Items"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

step 2
insert PivotTable from external data source (eg. Query - Table1)