EXCEL PivotTable

Question

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

Answer 1

If not sure how to use Power Query, or if you don't have access to it, then manually unpivot your data. Basically you want it took like the Reformatted data below.

Answer 2

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.