Forum Discussion

av9's avatar
av9
Helper III
5 years ago
Solved

Remove duplicate rows in data based on column values

Hi

I am trying to clean up the duplicate rows in my data in Power Query. 

This is a sample of the data imported.

IDCustomer IDNameSubscriptionSubscriber DateSubscriber Status 
1CUST01Ferris LoMonthly Newsletter1/8/2020Activekeep
2CUST01Ferris LoMonthly Newsletter1/5/2019Inactiveremove
3CUST02John SmithMonthly Newsletter1/5/2019Activekeep
4CUST03Jim JamesMonthly Newsletter1/1/2018Activekeep
5CUST04Pat HamMonthly Newsletter1/6/2020Inactivekeep

6

CUST04Pat HamMonthly Newsletter1/5/2019Inactiveremove

 

and I would like it to end up like this:

based on criteria;

IF Customer ID is duplicated, and Subscriber status = Active, remove rows where Subscriber status = Inactive

IF Customer ID is duplicated, and Subscriber status = Inactive, remove oldest Subscriber Date rows (i.e. keep max date)

IDCustomer IDNameSubscriptionSubscriber DateSubscriber Status
1CUST01Ferris LoMonthly Newsletter1/8/2020Active
3CUST02John SmithMonthly Newsletter1/5/2019Active
4CUST03Jim JamesMonthly Newsletter1/1/2018Active
5CUST04Pat HamMonthly Newsletter1/6/2020Inactive

 

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    You can try this in new query and adjust accordingly.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIODQ4xADHcUouKMosVfPKBbN/8vJKMnEoFv9Ty4pzUkpLUIqCgob6FvpGBkQGQ6ZhcklmWqhSrE61kRKIZpkAzDC2BTM+8RIQpxjBTQMZ55WfkKQTnZpZkEGEMklNMYIaATPPKzFXwSsxNLcZthiHIDAtUM0xhZoAMC0gsUfBIzMVtghksQFA8Y0aSGVgCJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Customer ID" = _t, Name = _t, Subscription = _t, #"Subscriber Date" = _t, #"Subscriber Status" = _t]),
    Grouped = Table.Group(Source, {"Customer ID"}, {{"GR", each if List.Contains(List.Distinct(_[Subscriber Status]),"Active")
then Table.SelectRows(_, each ([Subscriber Status] = "Active"))
else Table.FirstN(Table.Sort(_,{{"Subscriber Date", Order.Descending}}),1)}}),
    Removed = Table.RemoveColumns(Grouped,{"Customer ID"}),
    FINAL = Table.ExpandTableColumn(Removed, "GR", {"ID", "Customer ID", "Name", "Subscription", "Subscriber Date", "Subscriber Status"}, {"ID", "Customer ID", "Name", "Subscription", "Subscriber Date", "Subscriber Status"})
in
    FINAL

2 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    You can try this in new query and adjust accordingly.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIODQ4xADHcUouKMosVfPKBbN/8vJKMnEoFv9Ty4pzUkpLUIqCgob6FvpGBkQGQ6ZhcklmWqhSrE61kRKIZpkAzDC2BTM+8RIQpxjBTQMZ55WfkKQTnZpZkEGEMklNMYIaATPPKzFXwSsxNLcZthiHIDAtUM0xhZoAMC0gsUfBIzMVtghksQFA8Y0aSGVgCJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Customer ID" = _t, Name = _t, Subscription = _t, #"Subscriber Date" = _t, #"Subscriber Status" = _t]),
    Grouped = Table.Group(Source, {"Customer ID"}, {{"GR", each if List.Contains(List.Distinct(_[Subscriber Status]),"Active")
then Table.SelectRows(_, each ([Subscriber Status] = "Active"))
else Table.FirstN(Table.Sort(_,{{"Subscriber Date", Order.Descending}}),1)}}),
    Removed = Table.RemoveColumns(Grouped,{"Customer ID"}),
    FINAL = Table.ExpandTableColumn(Removed, "GR", {"ID", "Customer ID", "Name", "Subscription", "Subscriber Date", "Subscriber Status"}, {"ID", "Customer ID", "Name", "Subscription", "Subscriber Date", "Subscriber Status"})
in
    FINAL
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Seems that you could Group on Customer ID using the GUI function, choosing "All Rows" as the aggregation.  Let's say you named the Grouped table column "Details".  Then you can filter for each Max Date per Customer ID:

    Filtered = Table.SelectRows(PreviousStepName, each Table.Max([Details], "Subscriber Date"))

    Then keep only the "Details" column, and expand it. This also eliminates the need to care what the active/inactive status, since you are returning each latest status.

     

    --Nate