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
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!