Remove duplicated rows based on values of other columns

[robsmith1980]

Hi,

I hope someone can help.

I have a dataset as summarised below - the actual data sets is many thousands of rows and covers 5 years worth of data.

Where the Subscriptionid, USD and Date columns ALL contain dupicate values, i want to remove any row with the PartnerAttributionType of "Record" - IE for the subcriptionids below, I just want to be left with the rows showing Admin

The same subscriptionid could have the same USD value on multiple dates, so i can't just remove duplicates, etc.

All help greatly appreciated!

Subscriptionid	USD	PartnerAttributionType	Date
E10000838740	4500	Admin	30/09/2020
E10000838740	4500	Record	30/09/2020
E6474849	6000	Admin	30/09/2020
E6474849	6000	Record	30/09/2020
E98979987	5000	Admin	30/09/2020
E36393937	5000	Record	30/09/2020
E10000838740	8000	Admin	28/08/2020
E10000838740	8000	Record	28/08/2020
E6474849	2500	Admin	28/08/2020
E6474849	2500	Record	28/08/2020
E98979987	7000	Admin	28/08/2020
E36393937	7000	Record	28/08/2020

[manikumar34]

@robsmith1980 

You can also use the bwlow M query to do that. 

= Table.SelectRows(#"Changed Type", each [PartnerAttributionType] = "Admin")

Regards, 

Manikumar

[themistoklis]

Hello @robsmith1980 

In Power Query editor do right click on the value 'Record' --> Text Filters --> Does Not Equal

See image below

[robsmith1980]

Thanks - just to be 100% clear, will that only remove the rows where "SubscriptionID", "USD" and "Date" all contain duplicate values?

IE If a SubscriptionID has two rows showing the same USD and Date Value, but different PartnerAttirbutionTypes, it will just remove the row where PartnerAttributrionType = "Record"?

There will be instances where one of the Sub ID, USD or Date columns isn't duplicated, and these would need to remain

[themistoklis]

@robsmith1980 

Have you tried any of the actions we described above to see if they cover your needs?

You mentioned 

Where the Subscriptionid, USD and Date columns ALL contain dupicate values, i want to remove any row with the PartnerAttributionType of "Record" - IE for the subcriptionids below, I just want to be left with the rows showing Admin

---

My understanding is that you want to get rid of records with value = 'Record'. Am i right?

[robsmith1980]

@themistoklis  - No, not quite, I am afraid - All of the solutions so far just remove the rows showing "Record" under Partner Attribution Type - apologies if have not explained it clearly

The data set contains a list of Subscriptions, showing a USD value, a bill date and a Partner Attribution Type.

The same subscription ID will appear on multiple dates and may or may not have the same USD value.

Some subscriptions may have rows showing both "Record" and "Admin", some will just have one or the other.

I need to remove rows from the dataset where the Subscription ID AND the USD Value AND the Date column are duplicated, but where the Partner Attribution Type is set to "Record"  - The below table is perhaps a better representation of the true dataset

Subscriptionid	USD	PartnerAttributionType	Date
E10000838740	4500	Admin	30/09/2020
E10000838740	4500	Record	30/09/2020
E6474849	6000	Record	30/09/2020
E98979987	5000	Admin	30/09/2020
E36393937	5000	Record	30/09/2020
E10000838740	8000	Admin	28/08/2020
E10000838740	8000	Record	28/08/2020
E6474849	2500	Admin	28/08/2020
E98979987	7000	Admin	28/08/2020
E36393937	7000	Record	28/08/2020

[manikumar34]

@robsmith1980 

Try using Remove rows > Remove Duplicates which will remove the columns which seem like duplicated on the entire table.

If you want delete the records whcih are duplicated and are on Record type, I afraid that I don't have a solution for now, need toc check with this. Let me know if the above option works else let's check further to get you the correct solution. 

Regards, 

Manikumar

[manikumar34]

@robsmith1980 , 

If your data is same like as you shared here and wants to remobve Record type and keep only Admin data, Use Power query. 

Remove Rows>Remove alternative rows. 

Regards, 

Manikumar

[robsmith1980]

Unfortunately, its not that simple - there are multiple rows and not every alternative row needs to be removed