Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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.
Solved! Go to Solution.
Hi @dlopez004 ,
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
Hi @dlopez004 ,
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
pivot RatingDate
This worked! I knew it was a simple solution. Thanks!