CODEWITHSUNDEEP
excelpowerquery
Ron Rosenfeld
Ron Rosenfeld

Reputation: 60494

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:

enter image description here

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.

enter image description here

What am I doing incorrectly?

Upvotes: 1

Views: 377

Answers (1)

QHarr
QHarr

Reputation: 84475

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:

Image of query

Step 3:

Step2

Step 5:

Step 5

Upvotes: 1

Related Questions