Custom list sorting for duplicate values

[Anonymous]

Hi all, 

For example, I have this data from a review process: 

ID	Rating
57795	A
57795	Pass
57795	Pass
57795	A

What I want to do is have a single result returned for ID 57795 of 'A' (other alternatives are B, C, Pass and Fail) - there's quite a few records and I wondered if there was a Power Query that I could use that would look at the ID then the ratings column, and then provide the highest rating for each ID once. 

Thanks!

[SamInogic]

Hi,

Im a bit unclear about Your requirement but It seems you are Looking For a single row for Id and all the ratings of the Id in the same row,

If this is your Requirement then try this out ,

This is my Sample Table,

Use group by as in below Screenshot,

There will be a error,

To solve this us below Expression

= Table.Group(#"Changed Type", {"ID"}, {{"Result", each Text.Combine([Rating],","), type nullable

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

Thanks - very useful, to confirm I would just want the highest rating returned, but this is a useful start!

[SamInogic]

Hi @Anonymous , 

If you want the highest Ratings so instead of Combining, you need to Use Max of ratings in Group by as in Screenshot,

Note that we are considering that your Ratings is Numerical column.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[JohnShepherdAPD]

Hi this example should give you what you want, however, if you want a different order of ratings then add an integer column for every rating and group by that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU3tzRV0lFyVIrVQfACEouL8QtA1RsbGZoAeU4oPLfEzByl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Rating", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Rating", each List.Min([Rating]), type nullable text}})
in
    #"Grouped Rows"

[Anonymous]

Thanks for this - looks good, although I'll need to look at the code so I can understand it myself (it's really appreciated that you've provided this, I just need to understand it so I can apply it to similar situations in future!).

[ToddChitt]

Think how you might do this in real life: You would GROUP BY the ID column and take the MIN of the Rating column, or whatever aggregation gets you A values instead of B. Create a DAX (or Power Query) table that does that aggregation.

If you sort your distint list of Ratings, the list would be, in alphabetical order, A, B, D, Fail, Pass (because the F in Fail comess before the P in Pass). If you want Pass to come before Fail, then you should probably convert the letters to numbers: A=1, B=2, C=3, Pass=4, Fail=5, and get the MIN of the number.

[Anonymous]

Thanks for this guidance - it's helpful to have someone simply explain to my what I'm trying to do in terms that I can apply to PowerBI! I had hit a wall and had no one to speak with, but the way you've described this is really useful.