Custom list sorting for duplicate values

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.

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"

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 [email protected]

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

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