Forum Discussion
Solved
Remove duplicate and control which row stays
Hi! I have a pretty simple query, two columns. Column A is the name of a school and column B is an ID number. I want to remove the duplicate ID number, but keep the row that has the shortest name. T...
NewStep=Table.FromRecords(Table.Group(PreviousStepName,"ColumnB",{"n",each Table.Min(_,each Text.Length([ColumnA]))})[n])
You can Group by ID, then use a custom aggregation to return the grouped subtable that has the shortest "school"
let
//change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"school", type text}, {"id", Int64.Type}}),
//group by id and filter table by shortest text length of school
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
{"School", (t)=> Table.SelectRows(t,
each Text.Length([school]) = List.Min(List.Transform(t[school], each Text.Length(_)))),
type table[school=text, id=Int64.Type]}
}),
//remove unneeded column and expand the list of tables
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"id"}),
#"Expanded School" = Table.ExpandTableColumn(#"Removed Columns", "School", {"school", "id"})
in
#"Expanded School"