Forum Discussion
Help with duplicates rows
Good morning friends,
My database looks like the one below.
If the "name" column has some duplicates, like for example Luca's 3 rows and Tom's 2, I need to leave only 1 row for each. And the row that I need to leave is the one with the column "situation" = work.
Can you help me?
NAME | ROLE | Unity | Situation | Fired date |
| Jhon | fighter | Team 11 | Fired | 10/04/2022 |
| Luca | apprentice | Migration | Fired | 05/02/2021 |
| Luca | fighter | Team 12 | Work | |
| Luca | trainee | Migration | Fired | 05/03/2021 |
| Joseph | driver | Team 11 | Fired | 06/07/2021 |
| Tom | apprentice | Migration | Fired | 06/08/2021 |
| Tom | driver | Team 14 | Work | |
| Amanda | fighter | Team 13 | Work |
Hi Anonymous
For duplicated rows, you have only 1 row with [Situation]="Work"? Added a custom column to check the condition and filter based on it, paste the M code in Advanced Editor via a blank query to have a look
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9PC8IwDMW/ivQsNO3mn6sXD0NvAw9jh7LFLcjaEauf3yjIRlHxlJeQ33u8qlJFH7xaqjN1fUQWVaIbFsaI2hNjK9OAhlxbsFbVy0odbo2TqxtHRh+pQVmO1LGL9LJ6Y7DSYJ+YmWNpkBV1CnyRMX+L7MjjL+tssi7CFcde7i3T/UsJWGvYTEgZhr86CLVNqSQlTxrsBufbT1Wz+WP9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, ROLE = _t, Unity = _t, Situation = _t, #"Fired date" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=>[a= List.Count( List.Select(Source[NAME],each _=x[NAME])), b=if a=1 then 1 else if x[Situation] = "Work" then 1 else 0][b]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"
1 Reply
Hi Anonymous
For duplicated rows, you have only 1 row with [Situation]="Work"? Added a custom column to check the condition and filter based on it, paste the M code in Advanced Editor via a blank query to have a look
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9PC8IwDMW/ivQsNO3mn6sXD0NvAw9jh7LFLcjaEauf3yjIRlHxlJeQ33u8qlJFH7xaqjN1fUQWVaIbFsaI2hNjK9OAhlxbsFbVy0odbo2TqxtHRh+pQVmO1LGL9LJ6Y7DSYJ+YmWNpkBV1CnyRMX+L7MjjL+tssi7CFcde7i3T/UsJWGvYTEgZhr86CLVNqSQlTxrsBufbT1Wz+WP9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, ROLE = _t, Unity = _t, Situation = _t, #"Fired date" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (x)=>[a= List.Count( List.Select(Source[NAME],each _=x[NAME])), b=if a=1 then 1 else if x[Situation] = "Work" then 1 else 0][b]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"