Forum Discussion
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
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
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
Pivoted
Please 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
v-dineshya I was able to download your file. Forgive me, but I am just a bit confused. I do see your M code but I can't type out every student's test & scores. Apologies if I am misunderstanding! Thanks so much!
