EXCEL PivotTable

October 18, 2022

DATA TAB

  SCHOOL    STUDENT SCORE1  SCORE2
    A   1   PASS    FAIL
    A   2   PASS    FAIL
    A   3   PASS    PASS
    A   4   FAIL    PASS
    B   5   FAIL    PASS
    B   6   PASS    PASS
    B   7   PASS    PASS
    B   8   FAIL    PASS
    B   9   FAIL    PASS
    B   10  FAIL    FAIL

PIVOT TAB

SCHOOL  SCORE1      SCORE2  
    PASS    FAIL    PASS    FAIL
A   3   1   2   2
B   2   4   5   1

I have a Excel with the DATA TAB and I am wondering, how do I use a pivot table to get the table show in the PIVOT TAB

I try using SCHOOL as ROW and SCORE1 and SCORE2 as COLUMN but it does not produce the outpu

>Solution :

Use Power Query to first unpivot the SCORE1 and SCORE2 columns, after which it will be possible to set up your desired Pivot Table.

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

Obviously you can add further steps to the above to rename columns, etc.