Pivoting only working sometimes

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
Pivoted

Please 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