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. ID Customer ID Name Subscription Subscriber Date Subscriber Status   1 ...
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
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