Pivoting only working sometimes

[afaherty]

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!

[Ashish_Mathur]

Hi,

Before the pivoting step, ensure that the Student ID column is sorted in ascending order.

[v-dineshya]

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

[afaherty]

@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.

[v-dineshya]

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

[afaherty]

@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!

[v-dineshya]

Hi @afaherty ,

Thank you for the update. Could you please confirm that, from which data source you are pulling the data , For example from excel, csv , SQL server, share point or another api, based on the data source i will change the M code. 

Regards,

Dinesh

[afaherty]

@v-dineshya Sure, I import from an Excel file. Thanks!

[v-dineshya]

Hi @afaherty ,

Please refer below updated M code. I took sample excel, i have imported excel data into Power bi.

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"}
}
),

ChangeType = Table.TransformColumnTypes(
RenamedCols,
{{"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 output snap.

Please replace above file path with your excel file path.  Please refer output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[afaherty]

@v-dineshya Thank you! I do have one step that occurs before your M code, and that is to filter out any scores of null, blank, or N/A. I am still a newbie to M, so I am not sure how to incorporate my filtering step along with your M code above. Thanks again!

[v-dineshya]

Hi @afaherty ,

Thank you for the update. As per your requirement i have provided the solution. If you have new requirement , please create a new thread based on your requirement in Fabric community. Our community members will assit on it.

Regards,

Dinesh

[v-dineshya]

Hi @afaherty ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @afaherty ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[afaherty]

@v-dineshya Hello, sorry, I just didn't want to make a whole separate post asking how to nest 2 "let"s/"in"s.

[v-dineshya · Accepted solution]

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

[v-dineshya]

Hi @afaherty ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[SundarRaj]

Hi @afaherty,

This could happen if your StudentID is a text data type. Probably because of the text not being trimmed of extra spaces, it might recogonize same number as different rows. I'll attach the example photo for your reference. Let me know if this was helpful. Thanks

[afaherty]

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.

[SundarRaj]

Hello, can you share the real dataset you're working with that's causing the issue?

[afaherty]

I wish I could, but unfortunately I can't, as it's for my job.