Forum Discussion

Nozama's avatar
Nozama
Helper I
1 year ago
Solved

Query on removing duplicates

I have a table like so:  I would like to remove duplicate rows such that I only keep the document which has the highest revision. Any help is appreciated. Result:  Thank you.
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    Hi Nozama, check this:

     

    Output

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgIBJR0lQ6VYnWglQxAA8ozAPJicEYqcMYqcCZhnDAIYPFMUfWYochZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Document = _t, Revision = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Revision", Int64.Type}}),
    KeepMaxRevisionRow = Table.Combine(Table.Group(ChangedType, {"Document"}, {{"T", each Table.MaxN(_, {"Revision"}, 1), type table}})[T])
in
    KeepMaxRevisionRow
dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi Nozama, check this:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgIBJR0lQ6VYnWglQxAA8ozAPJicEYqcMYqcCZhnDAIYPFMUfWYochZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Document = _t, Revision = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Revision", Int64.Type}}),
    KeepMaxRevisionRow = Table.Combine(Table.Group(ChangedType, {"Document"}, {{"T", each Table.MaxN(_, {"Revision"}, 1), type table}})[T])
in
    KeepMaxRevisionRow