Summary pivot table from matrix data

Question

I have an excel file that contains a table formatted in this way (the example here below is just notional):

Table 1

Column A	Column B	Column C
Group A	Option 12	Option 33
Group B	Option 11	Option 15
Group B	Option 12	Option 13
Group B	Option 1	Option 10
Group A	Option 1	Option 13
Group B	Option 12	Option 15

Out of this table, I would like to create a Pivot table that provides the following summary (basically shows the count for each option, grouped by the Group)

Table 2

-	Option 1	Option 10	Option 11	Option 12	Option 13	Option 15	Option 33
Group A	1	0	0	1	1	0	1
Group B	1	1	1	2	1	2	0

In order to obtain this result, I found that I need to "unroll" Table 1 like this (selecting Table 1 does not produce the expected results in Table 2):

Table 3

Column A repeated twice	Column B/C merged
Group A	Option 12
Group B	Option 11
Group B	Option 12
Group B	Option 1
Group A	Option 1
Group B	Option 12
Group A	Option 33
Group B	Option 15
Group B	Option 13
Group B	Option 10
Group A	Option 13
Group B	Option 15

Does anyone know if is it possible to get Table 2 results using Table 1 data, instead of Table 3 ?

Thanks

It seems that Table 2 is not properly visualized; I tried to modify it, but I was not allowed to do so — FancyPants, Jun 17 '23 at 17:42

score 3 · Accepted Answer · answered Jun 18 '23 at 00:26

The entire transformation can also be accomplished using Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", type text}}),

//Unpivot columns except for the first
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column A"}, "Attribute", "Value"),

//Remove Attribute column
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),

//Make a list of the Options which will be used as column headers in the Pivoted table
//This will be sorted alphabetically, but could be sorted alphanumerically if necessary
    colHdrs = List.Sort(List.Distinct(#"Removed Columns"[Value])),

//Group by "Group" in Column A
//Then aggregate by Pivoting each sub group
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Column A"}, {
        {"Pivot", each Table.Pivot(_, colHdrs, "Value","Value", List.Count)}}),

//Expand the aggregated group pivots
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", colHdrs),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Pivot", List.Transform(colHdrs, each {_, Int64.Type}))
in
    #"Changed Type1"

score 1 · Answer 2 · answered Jun 17 '23 at 18:07

Use Power Query to unpivot your data, then create the new pivot table.

With your data selected, go to Data > Get & Transform Data > From Table / Range. Excel will format it as a table and open the Power Query editor.

In the Power Query editor, right click Column A > Unpivot other columns. Right click the "Attribute" column > Remove. Then click Close & Load. That will give you the green table in Excel.

From the new table in Excel, insert a pivot table. The Value field should go in Columns and Values, and the Column A field should go in Rows.

Summary pivot table from matrix data

2 Answers2