Forum Discussion
Zabeer
4 years agoHelper I
Remove duplicates based on multiple columns
Hello, I need help filtering out duplicates based on multiple columns. Sample dataset : PO Decision Amount Createdon A Repair 500 1/1/2021 A Repair 500 20/5/2021 A Test...
- 4 years ago
You can do this in two steps. First, group by PO, Decision, Amount taking the minimum over Createdon. Then group by just PO taking the maximum over Createdon using one of the methods I describe in this blog post:
Select Distinct Rows Ordered by Another Column -- Power Query Edition
Here's an example query with both steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKLUjMLAIyTA0MgKShvqG+kYGRoVKsDlZ5IwN9U1QFIanFJXBpYwN9QyOEvBMWA4z1DQ1AKgwwVRgaQd1gjsMIM4gRpvoGSCqcEY6wgPnBACHtgmKDAUSFGbIVOFTADYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t, Decision = _t, Amount = _t, Createdon = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Decision", type text}, {"Amount", Int64.Type}, {"Createdon", type date}}, "en-IN"), #"Grouped Rows" = Table.Group(#"Changed Type", {"PO", "Decision", "Amount"}, {{"Createdon", each List.Min([Createdon]), type nullable date}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"PO"}, {{"MaxDate", each Table.Max(_, "Createdon"), type record}}), #"Expanded MaxDate" = Table.ExpandRecordColumn(#"Grouped Rows1", "MaxDate", {"Decision", "Amount", "Createdon"}, {"Decision", "Amount", "Createdon"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded MaxDate",{{"Decision", type text}, {"Amount", Int64.Type}, {"Createdon", type date}}) in #"Changed Type1"
AlexisOlson
4 years agoSuper User
You can do this in two steps. First, group by PO, Decision, Amount taking the minimum over Createdon. Then group by just PO taking the maximum over Createdon using one of the methods I describe in this blog post:
Select Distinct Rows Ordered by Another Column -- Power Query Edition
Here's an example query with both steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpKLUjMLAIyTA0MgKShvqG+kYGRoVKsDlZ5IwN9U1QFIanFJXBpYwN9QyOEvBMWA4z1DQ1AKgwwVRgaQd1gjsMIM4gRpvoGSCqcEY6wgPnBACHtgmKDAUSFGbIVOFTADYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t, Decision = _t, Amount = _t, Createdon = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Decision", type text}, {"Amount", Int64.Type}, {"Createdon", type date}}, "en-IN"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PO", "Decision", "Amount"}, {{"Createdon", each List.Min([Createdon]), type nullable date}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"PO"}, {{"MaxDate", each Table.Max(_, "Createdon"), type record}}),
#"Expanded MaxDate" = Table.ExpandRecordColumn(#"Grouped Rows1", "MaxDate", {"Decision", "Amount", "Createdon"}, {"Decision", "Amount", "Createdon"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded MaxDate",{{"Decision", type text}, {"Amount", Int64.Type}, {"Createdon", type date}})
in
#"Changed Type1"