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

3 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi Nozama Ypu could achive this by Group By Please try this

    • Select the Document column.
    • Click Home > Group By.
    • In the Group By dialog:
      • Set "Group By" to Document.
      • Add an aggregation operation for Revision with Max.
        If this post helped plpease do give a kudos and accept this as a solution
        Thanks In Advance

     

     

    • Nozama's avatar
      Nozama
      Helper I
      1 year ago

      Thanks for that. I think it worked (sort of). I added the step but the result got rid of all the other columns in the table!

  • 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