cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kmoxley Frequent Visitor
Frequent Visitor

Unpivoting to keep the key id unique

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         #                  #

1 REPLY 1
Super User
Super User

Re: Unpivoting to keep the key id unique

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"
Specializing in Power Query Formula Language (M)