Q:

Power Query Pivot loses rows

I am trying to replicate in Get & Transform something that is easily done in Excel Pivot Tables.   But when I try to Pivot the Category column, I only get a single row. 
Original Data
| Course         | Category |
|----------------|----------|
| English        | NL       |
| Mathematics    | CCA      |
| Social Studies | STS      |
| Social Studies | SS       |
| History        | STS      |
| History        | CCA      |
| Physics        | STS      |
| Geology        | SS       |
| Geology        | CCA      |

In Excel, I can easily generate a desired Pivot Table by dragging:

Course to the Rows area
Category to the Columns area
Category to the Values area

But if I try to Pivot Category column in Get and Transform, the new columns are created, but all of the counts are summarized; not the counts per course.   (And if I choose Don't Aggregate, I get an error. 

What am I doing incorrectly? 

A:

Such as 
M code:
let
    Source = Excel. CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table. TransformColumnTypes(Source,{{" Course         ", type text}, {" Category ", type text}}),
    #"Grouped Rows" = Table. Group(#"Changed Type", {" Course         "}, {{"Count", each _, type table}}),
    #"Expanded Count" = Table. ExpandTableColumn(#"Grouped Rows", "Count", {" Course         ", " Category "}, {"Count.  Course         ", "Count.  Category "}),
    #"Pivoted Column" = Table. Pivot(#"Expanded Count", List. Distinct(#"Expanded Count"[#"Count.  Category "]), "Count.  Category ", "Count.  Course         ", List. Count)
in
    #"Pivoted Column"

Result:

Step 3:

Step 5:

