Forum Discussion
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 column path 2.2 while keeping the 'Transform File' columns intact.
Any ideas?
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
6 Replies
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 CombinedThimios, 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.
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.
