Unpivoting to keep the key id unique

[kmoxley]

My Data looks like this

ID Grade SubjectScore1 Score2

1   2           Math   #             #

1  2             ELA   #             #

2  3            Math  #             #

2  3              ELA   #             #

I need it to look like this

ID Grade ELA.Score1 MAth.Score1

1     2          #                  #

2      3         #                  #

[MarcelBeug]

Try this query:

let
    Source = MyData,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Score2"}),
    #"Added Suffix" = Table.TransformColumns(#"Removed Columns", {{"Subject", each Text.From(_, "en-US") & ".Score1", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[Subject]), "Subject", "Score1")
in
    #"Pivoted Column"