Custom column based on ranking

[Anonymous]

I need to create a new column called tech_id_padu in the below example.  I need the column to take the highest rank for each askID and return the rating.  I'm not sure how to write the logic for this.  I'm assuming I need to create a custom column and write some code to get this to work.

[Anonymous]

Here's some sample data.  I wasn't able to get it working.  I filled in the tech_id_padu column with what the custom column should display.  Thanks

askId	aggregateTechPaduRating	softwareName	rating	tech_ld_rank	tech_id_padu
U006626	Discouraged	Anacron	NotRated	0	Unacceptable
U006626	Discouraged	Apache Ant	Discouraged	3	Unacceptable
U006626	Discouraged	Chef	Preferred	1	Unacceptable
U006626	Discouraged	Firewall	Preferred	1	Unacceptable
U006626	Discouraged	Foomatic	NotRated	0	Unacceptable
U006626	Discouraged	IBM Business Process Manager Advanced	Acceptable	2	Unacceptable
U006626	Discouraged	IBM Global Security Kit	NotRated	0	Unacceptable
U006626	Discouraged	IBM JDK	Unacceptable	4	Unacceptable
U006626	Discouraged	IBM WebSphere Application Server	Discouraged	3	Unacceptable
U006626	Discouraged	IBM WebSphere Enterprise Service Bus - ESB	Unacceptable	4	Unacceptable
U006626	Discouraged	IPT	Acceptable	2	Unacceptable
U006626	Discouraged	Red Hat JBoss EAP	Acceptable	2	Unacceptable
U018813	Discouraged	Anacron	NotRated	0	Discouraged
U018813	Discouraged	Apache Lucene	NotRated	0	Discouraged
U018813	Discouraged	Apache Tomcat	Preferred	1	Discouraged
U018813	Discouraged	CMSutil	NotRated	0	Discouraged
U018813	Discouraged	Chef	Preferred	1	Discouraged
U018813	Discouraged	Finger	NotRated	0	Discouraged
U018813	Discouraged	Firewall	Preferred	1	Discouraged
U018813	Discouraged	FxLoad	NotRated	0	Discouraged
U018813	Discouraged	Mootools	Discouraged	3	Discouraged

[tackytechtom]

Hi @Anonymous ,

Thank you for sharing the data!

Here a solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZTJbsIwEEB/xco5lViqiGvC0hZIFRFQD4iD4wxgyXiiiUPbv68Jh5aWogQfLC/jeR7Ptl57q04nCHqB53sjWQqsiO8gt7tQc0Go7eoVzYKb+rBjx8oKBBSGZwq8jX+DUHCxBxZq80fUb8MZ7mFrp4RgC0T1UbeN/kQSvHOlnBiIB26kcHDHSxSzqCqlhrJkCaE4zTHXVkwszI9ci7PbvnG+12v7wpPCjCuWgqhImk82k8bR5ulo9lvF9x7bUt4gS4s9kM2HolBSWGeitmbSEcgtPS7pY22ACpIl1HApgD2c3M7GaeT6jWTpFJ4F5OyZGzbN0EZ+HCYNad3BoNtvXp8/r90EnMtzXgnQ4IxZ4sHG9EqJNeUM47QyUt1vyD9toqn6ROpdnYp3Pn+jyzRGfMyR5/ebECMaRFVeracLyOYL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [askId = _t, aggregateTechPaduRating = _t, softwareName = _t, rating = _t, tech_ld_rank = _t, tech_id_padu = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"askId", type text}, {"aggregateTechPaduRating", type text}, {"softwareName", type text}, {"rating", type text}, {"tech_ld_rank", Int64.Type}, {"tech_id_padu", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"askId"}, {{"MaxTechIDRank", each List.Max([tech_ld_rank]), type nullable number}, {"Rating", each _, type table [askId=nullable text, aggregateTechPaduRating=nullable text, softwareName=nullable text, rating=nullable text, tech_ld_rank=nullable number, tech_id_padu=nullable text]}}),
    #"Expanded Rating" = Table.ExpandTableColumn(#"Grouped Rows", "Rating", {"rating", "tech_ld_rank"}, {"Rating.rating", "Rating.tech_ld_rank"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Rating", "FilterColumn", each if [MaxTechIDRank] = [Rating.tech_ld_rank] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([FilterColumn] = 1)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"askId"}, #"Removed Duplicates", {"askId"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Rating.rating"}, {"Grouped Rows.Rating.rating"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Grouped Rows",{{"Grouped Rows.Rating.rating", "tech_id_padu_tomsCol"}})
in
    #"Renamed Columns"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

askID	software	rating	tech_Id_rank	tech_id_padu
U006626	Foomatic	Acceptable	2	Unacceptable
U006626	IBM JDK	Unacceptable	4	Unacceptable
U018813	Firewall	Preferred	1	Discouraged
U018813	Mootools	Discouraged	3	Discouraged

[tackytechtom]

Hi @Anonymous ,

I'd suggest to do a grouping on askId with MAX tech_id_rank. Then join the result set with the original one and you should be good to go.

You may share some sample data or the file and I can support you further 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/