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 th...
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"
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"