Forum Discussion
Helper IRemove Duplicates for filtered data then unfilter
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.