Selective row based on many scenarios

Hi erikmathijsen,

In your example what was the criteria that you used to choose the first row of SKU B67293 instead of second row? 
Both rows have type = Unexpected?

Hi erikmathijsen ,

Here is my solution:

1. I'm using this data set:

2. My M script code is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYOVdJRCsrPSXXOT0kFMgOKMssycxLTQeyQyoJUpVidaCUnM3MjS2OgiGNAfLh/kLdrULyXvxMKPyDIMwwoEOrnGhHg6hzi6oKh0dfRz9EdWSdMwDHI1RGXfgtDU0MDEvQ7+/uFBDli1Q9yqI+/owuq08EiEUA73XEZYWloYGpOqt9jAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"SKU", type text}, {"RoleCode", type text}, {"Privilage", type text}, {"Type", type text}}),
    GroupTable = Table.Group(ChangedType, {"Privilage"}, {
        {"GroupTable", each if List.ContainsAny([Type], {"CONTRACTED"}) then Table.SelectRows(_, each [Type] = "CONTRACTED") else _}
    }),
    ExpandTable = Table.ExpandTableColumn(GroupTable, "GroupTable", {"SKU", "RoleCode", "Privilage", "Type"}, {"SKU", "RoleCode", "Privilage.1", "Type"}),
    RemoveColumns = Table.RemoveColumns(ExpandTable,{"Privilage"}),
    RenameColumn = Table.RenameColumns(RemoveColumns,{{"Privilage.1", "Privilage"}}),
    Result = Table.TransformColumnTypes(RenameColumn,{{"Privilage", type text}, {"SKU", type text}, {"RoleCode", type text}, {"Type", type text}})
in
    Result

3. Final Output: