Forum Discussion
Remove Duplicates Based on String
Hi neilh
You can get the expected result with two steps easily. First group by person name column and perform All Rows operation on the new column named as "All Data".
Then add fx to create a custom step with the following code.
= Table.AggregateTableColumn(#"Grouped Rows", "All Data", {{"Status", List.Max, "Overall Status"}})My sample data is as below. "Status" is the column which marks "Complete" or "Incomplete" for every row.
Here is the full code. You can paste it into a blank query's Advanced Editor to check every step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRUggpSszMy8xLV3AE8ZzzcwtyUktSlWJ1MFU4gXieecnIakLycwmYgq7CCUOFc0ZRZjGmKWg2YarCcE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Course Name" = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course Name", type text}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"All Data", each _, type table [Name=nullable text, Course Name=nullable text, Status=nullable text]}}), #"Aggregated All Data" = Table.AggregateTableColumn(#"Grouped Rows", "All Data", {{"Status", List.Max, "Overall Status"}}) in #"Aggregated All Data"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hi neilh
Just need to modify the first step, switch to Advanced option and add a second new column Department with Max operation on Department. You can add multiple new columns with appropriate aggregations.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRUggpSszMy8xLV3AE8ZzzcwtyUktSgWxHpVgdTFVOIJ5nXjK6upD8XLymOWFV5YRVlXNGUWYxpmkotjrjUInpPqDKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Course Name" = _t, Status = _t, Department = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course Name", type text}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"All Data", each _, type table [Name=nullable text, Course Name=nullable text, Status=nullable text, Department=nullable text]}, {"Department", each List.Max([Department]), type nullable text}}), #"Aggregated All Data" = Table.AggregateTableColumn(#"Grouped Rows", "All Data", {{"Status", List.Max, "Overall Status"}}) in #"Aggregated All Data"Jing
Most Valuable ProfessionalFirst apply filter on last colum to keep only records which were not completed.
I think just applying the filter will solve your issue as there are only 2 records per user. If needed, select first column and remove duplicates. If duplicates are in 2nd column also for a user, then you will need to select first 2 columns and then remove duplictes.
Thank you for your reply! That definitely works for seeing only the users who didn't complete it. The thing is, I want to see users who did complete the training as well. If I apply a filter showing only records which were completed, it will include users who did not complete one of the two trainings.
- Vijay_A_Verma
I need to see the expected output for the given table before I can suggest the solution.
I basically want to take a bunch of data that looks like this:
Bob Training A Complete
Bob Training B Incomplete Tom Training A Complete Tom Training B Complete Chris Training A Incomplete Chris Training B Incomplete And turn it into this:
Bob Incomplete Tom Complete Chris Incomplete Thank you!
v-jingzhangCommunity Support
Hi neilh
You can get the expected result with two steps easily. First group by person name column and perform All Rows operation on the new column named as "All Data".
Then add fx to create a custom step with the following code.
= Table.AggregateTableColumn(#"Grouped Rows", "All Data", {{"Status", List.Max, "Overall Status"}})My sample data is as below. "Status" is the column which marks "Complete" or "Incomplete" for every row.
Here is the full code. You can paste it into a blank query's Advanced Editor to check every step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRUggpSszMy8xLV3AE8ZzzcwtyUktSlWJ1MFU4gXieecnIakLycwmYgq7CCUOFc0ZRZjGmKWg2YarCcE8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Course Name" = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course Name", type text}, {"Status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"All Data", each _, type table [Name=nullable text, Course Name=nullable text, Status=nullable text]}}), #"Aggregated All Data" = Table.AggregateTableColumn(#"Grouped Rows", "All Data", {{"Status", List.Max, "Overall Status"}}) in #"Aggregated All Data"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.