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

Thank you dufoq3 ,

I looked at your advanced editor guide, but this should be done somewhere in the middle of my transformation, not in the souce. How can I make it work?

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Thimios, you probably haven't read all my description from that screenshot. But to put my code into middle of yours you can just copy this part of my code:

    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])

 

Change selected Source to your Previous_Step reference, and your 1st next step after my query should refer my last step Combined.