Remove Duplicates for filtered data then unfilter

[dodonald]

I have a very big dataset with many steps in the query. I need to add a step to remove duplicates ONLY for tasks where:

• assigneerole = Change Administrator I, Change Administrator II, or Change Administrator III

AND

• status <> completed

In this example, I only want to keep 1 of the first 3 lines (it really doesnt matter to me which line, just that there are only 1) and preserve the other lines as they are.

subjectid	status	assignee_userid	assigneerole	createdon
1050052889	POTENTIAL	Person A	CHANGE ADMINISTRATOR I	10/11/2023
1050052889	POTENTIAL	Person B	CHANGE ADMINISTRATOR I	10/11/2023
1050052889	POTENTIAL	Person C	CHANGE ADMINISTRATOR I	10/11/2023
1050052889	POTENTIAL	Person A	CHANGE OWNER	10/11/2023
1153033046	Assigned	Person A	CHANGE ADMINISTRATOR I	10/1/2023
1177721261	Assigned	Person B	CHANGE OWNER	10/1/2023
1149606076	Assigned	Person C	CHANGE ADMINISTRATOR I	10/1/2023
1149606076	Assigned	Person C	CHANGE OWNER	10/1/2023
1156544940	COMPLETED	Person A	CHANGE ADMINISTRATOR I	9/1/2023
1125905697	COMPLETED	Person B	CHANGE ADMINISTRATOR I	9/1/2023

When I filter, then remove duplicates (on subjectid, status, assigneerole, and createdon), I get the results I want, but I'm not able to remove the filters (As I can in Excel). How can I remove duplicates based on these conditions? -OR- is there a way to combine the 3 duplicates into 1 row and preserve all the other data as desired? 

Note: The table has many more columns and rows than above; this is just representative for this problem.

[Anonymous · Accepted solution]

Hi @dodonald ,

I suggest you to try code as below to do some data transformation on your table.

= Table.Group(#"Changed Type", {"subjectid", "status", "assigneerole", "createdon"}, {{"assignee_userid", each Text.Combine([assignee_userid]," / "), type nullable text}})

Result is as below.

You can download the attachment file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.