Forum Discussion
Pivoting only working sometimes
Hello All,
I have spent hours searching for a solution to this and I am surprised I haven't found one!
I have this fake data here BUT it is important to note that my real data has many more columns and rows.
| StudentID | Test | Score |
| 1 | Math | 88 |
| 1 | English | 90 |
| 1 | Science | 83 |
| 1 | Social Studies | 97 |
| 2 | English | 95 |
| 2 | Social Studies | 68 |
| 3 | Math | 85 |
| 3 | English | 93 |
| 3 | Science | 86 |
| 4 | English | 93 |
| 4 | Science | 81 |
| 4 | Social Studies | 74 |
I want each studentID to have only 1 row, and the subjects to be in columns. So therefore, I pivoted this fake data based on test, and you may notice that since not every student has a row for every possible test. But still, it comes out just fine exactly how I want it:
| StudentID | Math | English | Science | Social Studies |
| 1 | 88 | 90 | 83 | 97 |
| 2 | 95 | 68 | ||
| 3 | 85 | 93 | 86 | |
| 4 | 93 | 81 | 74 |
But my question is - why is it that when I perform these steps on my REAL data, I do not get 1 row per student? It seems to be definitely due to the fact that I have lots more columns than my fake example here. Because when I take my actual data and isolate just the 3 columns I need (student ID, test, score), it works even though I have many more rows than my fake data here. For example, here is how it ends up with my real data:
| Math | English | Science | Social Studies | |
| 1 | 88 | null | null | null |
| 1 | null | 90 | null | null |
| 1 | null | null | 83 | null |
| 1 | null | null | null | 97 |
| 2 | null | 95 | null | null |
| 2 | null | null | null | 68 |
| 3 | 85 | null | null | null |
| 3 | null | 93 | null | null |
| 3 | null | null | 86 | null |
| 4 | null | 93 | null | null |
| 4 | null | null | 81 | null |
| 4 | null | null | null | 74 |
Does anyone have any ideas on why this may be occurring? Thanks!
Hi afaherty ,
Please refer below M code.
let
Source = Excel.Workbook(File.Contents("C:\Users\v-dineshya\Downloads\sample.xlsx"), true),
Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
RenamedCols = Table.RenameColumns(
PromotedHeaders,
{
{Table.ColumnNames(PromotedHeaders){0}, "StudentID"},
{Table.ColumnNames(PromotedHeaders){1}, "Test"},
{Table.ColumnNames(PromotedHeaders){2}, "Score"}
}
),AsText = Table.TransformColumnTypes(
RenamedCols,
{{"StudentID", Int64.Type}, {"Test", type text}, {"Score", type text}}
),Filtered = Table.SelectRows(
AsText,
each [Score] <> null and Text.Trim([Score]) <> "" and Text.Upper(Text.Trim([Score])) <> "N/A"
),WithNumber = Table.TransformColumns(
Filtered,
{{"Score", each try Number.From(_) otherwise null, type nullable number}}
),Pivoted = Table.Pivot(
WithNumber,
List.Distinct(WithNumber[Test]),
"Test",
"Score",
List.Max
)
in
PivotedPlease refer below sample data , M code , output snap and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
19 Replies
Hello, thanks so much for your input. That doesn't seem to be the problem. Upon launching Power Query, it does recognize the Student ID as a type of whole number. In your second screenshot (the Power Query one), it is showing exactly what I am encountering. Each student needs only 1 row but it's not cooperating.
Hello, can you share the real dataset you're working with that's causing the issue?
Hi afaherty ,
Thank you for reaching out to the Microsoft Community Forum.
Your pivot creates multiple rows because other columns besides StudentID are being treated as identifiers.
Solution: You need to make only StudentID as the identifier when pivoting.
In Power Query, Select only StudentID, Test, Score columns to pivot. After pivot, merge the pivoted result to the table if you still need the other columns.
Please refer below M code.
let
Source = Table.FromRows(
{
{"1","Math","88"},
{"1","English","90"},
{"1","Science","83"},
{"1","Social Studies","97"},
{"2","English","95"},
{"2","Social Studies","68"},
{"3","Math","85"},
{"3","English","93"},
{"3","Science","86"},
{"4","English","93"},
{"4","Science","81"},
{"4","Social Studies","74"}
},
{"StudentID","Test","Score"}
),ChangeType = Table.TransformColumnTypes(Source,
{{"StudentID", Int64.Type}, {"Test", type text}, {"Score", Int64.Type}}),Pivoted = Table.Pivot(
ChangeType,
List.Distinct(ChangeType[Test]),
"Test",
"Score",
List.Max
)
in
PivotedPlease refer below output snap and attached PBIX file.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
v-dineshya Hello, thanks so much for your help. When I select more than 2 columns, the "Pivot" button becomes greyed out, therefore I can't click it. I am also unable to open your PowerBI file, as my work has an older version installed. In regards to the M code - would I have to type all of that out? My actual dataset is huge.
Hi afaherty ,
Thank you for the update, Please try to install latest Power Bi desktop latest version from Microsoft store and I have created the M code based on sample data. I took the source as "Blank Query" in Query editor. Please confirm that, which source are you trying to connect. Based on your source , i will change my M code.
Regards,
Dinesh
Hi,
Before the pivoting step, ensure that the Student ID column is sorted in ascending order.
