Remove duplicate rows by another row

[Thimios]

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 column path 2.2 while keeping the 'Transform File' columns intact. 

Any ideas?

[Thimios]

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.

[d_rohlfs]

Hey @Thimios ,
You should just be able to do a sort descending on the file path 2.2, then use the remove duplicates function on the file path 2.1 column. Remove duplicates will not remove the first occurence of a value, but any subsequent occurrences. 

[dufoq3]

@d_rohlfs, to be sure you have to buffer table after sorting (because without buffering you never know which occurence will be deleted...)

[dufoq3 · Accepted solution]

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]

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]

@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.