Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Transpose Challenge

Hi Community, 

I'm trying to solve a transpose issue, if you look at the snapshot below, an employee has two records. One record is their current performance score and the next record is their previous performance score, ideally I'd like to have three columns per record. 
PersonID | Current Performance | Previous Performance

 

I know I know, this isn't best practice, but because I'll never bring in more than two performance scores it'll make things easier in my model. Any advice on how to achieve this would be greatly appreciated. Bonus points if the transpose has the headers as the date value. 

 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This worked! I knew it was a simple solution. Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Like this?

    Advanced editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvBCcAwDATBXu5tge4kpRmj/ttIjMEQ/NjXsHOC7plPJQa0ctEoC6LHT2Orbq1P8ihNl9bWWK+E7hc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, PerformanceRating = _t, RatingDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PersonID", Int64.Type}, {"PerformanceRating", Int64.Type}, {"RatingDate", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PersonID", Order.Ascending}, {"RatingDate", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"PersonID"}, {{"Data", each _, type table [PersonID=nullable number, PerformanceRating=nullable number, RatingDate=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", each Table.Pivot(Table.TransformColumnTypes(_, {{"RatingDate", type text}}, "en-US"),List.Distinct(Table.TransformColumnTypes(_, {{"RatingDate", type text}}, "en-US")[RatingDate]), "RatingDate", "PerformanceRating", List.Max)})
in
    Custom1

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • mussaenda's avatar
    mussaenda
    Community Champion
    2 years ago

    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