Forum Discussion

Thimios's avatar
Thimios
Helper III
2 years ago
Solved

Remove duplicate rows by another row

Hi all,   My table looks like this:                                             When same values in column path2.1, I need to keep rows that have the highest value in c...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Thimios,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjI0MjJT0lEyMFSK1UEVMYKJGBsYAjnIauAiSGqMDI3R1EBFMNWYKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.file_path.2.1 = _t, Column1.file_path.2.2 = _t]),
    GroupedRows = Table.Group(Source, {"Column1.file_path.2.1"}, {{"Max path 2.2", each Table.MaxN(_, "Column1.file_path.2.2", 1) , type table}}),
    Combined = Table.Combine(GroupedRows[Max path 2.2])
in
    Combined
Thimios's avatar
Thimios
Helper III
2 years ago

Hi all,

 

I appreciate your assistance.

I ended up creating a duplicate table, keeping only two first columns and grouping as suggested by dufoq3. Then, I merged the queries using Join Kind = Inner.

d_rohlfs suggestion works but I was not feeling confident that there will be no issues in the future.

Solution if dufoq3 is closer to the issue although not easy for us novice users.

 

Thank you all for helping.