Transpose Challenge

Hi Anonymous ,

you can create an index per person ID. Then pivot them.

So what i considered: 

if the personId has one row only, i am considering it as current. if this personID will have a new row in the future, it will automatically follow the rules created for personids with more than 1 row.

since the rows are limited only to max 2 per PersonID, i created an if then condition.

Try this: Copy the code and paste on a black query using advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDExMzVR0lEyAmJjQ31DI30jAyNDpVgd3LJGyLKmQBlDnHpN8eo1w6I3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, PerformanceRating = _t, RatingDate = _t]),
    #"Grouped Rows" = Table.Group(Source, {"PersonID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [PersonID=nullable text, PerformanceRating=nullable text, RatingDate=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "IndexPerPersonID", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"PerformanceRating", "RatingDate", "IndexPerPersonID"}, {"PerformanceRating", "RatingDate", "IndexPerPersonID"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Custom", {"PerformanceRating", "RatingDate"}, "Attribute", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each let 
_subindex = 
if [Count] > 1
and [IndexPerPersonID] = 1
then "Previous "
else 

if [IndexPerPersonID] = 2
then "Current "
else 

"Current "

in

_subindex &[Attribute]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"PersonID", "Custom", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Custom]), "Custom", "Value")
in
    #"Pivoted Column"

This may not be the cleanest solution but i hope it met your requirement