Getting values from Rows to Columns

[purplelily]

Hi! I have table here which I need to convert

From this:

Person	Rating Year	Rating
A	2017​	2​
A	2018​	3​
A	2019​	5​
B	2017​	1​
B	2018​	2​
B	2019​	4​
C	2017​	2​
C	2018​	3​
C	2019​	1​

to this:

Person	2017​	2018​	2019​
A
B
C

I tried Pivot Table but could not pick up just the value. What formula would I need to get the values in?

Thanks so much!!

purplelily

 

[mrshl9898]

Pivot should work. As long as Rating contains Values. Did you try this?

Another option (again, relies on Values)

 

[purplelily]

Ah ok, of course! - the pivot table. I always overthink it when it is so simple
My problem was I didn't realise the what I had in the ratings were not numbers but text. Need to convert them now.
Thanks so much and also for the formula!!!

purplelily

 

[sandy666]

with Power Query and Rating as text

Person	Rating Year	Rating		Person	2017	2018	2019
A	2017	2		A	2	3	5
A	2018	3		B	1	2	4
A	2019	5		C	2	3	1
B	2017	1
B	2018	2
B	2019	4
C	2017	2
C	2018	3
C	2019	1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Pvt = Table.Pivot(Table.TransformColumnTypes(Source, {{"Rating Year", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Source, {{"Rating Year", type text}}, "en-GB")[#"Rating Year"]), "Rating Year", "Rating")
in
    Pvt

but in this case you cannot sum text values

 

[purplelily]

Thanks!!

 

[sandy666]

Welcome!!