Remove duplicate and control which row stays

Hi, MS say "there's no guarantee that the first instance in a set of duplicates will be chosen when duplicates are removed" so best to write a function to control the behaviour. If you add the two queries below as blank queries you should see something that works.

Dummy data table - replace with your actual table

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitUBshBMJycg0wjChLOcgSxjCAvBdHFxAbJNIGwIKxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
// put table in memory to pass into fx below
#"aux - buffer table" = #"Changed Type",
#"Invoked fx - Shortest School Name" = Table.AddColumn(#"aux - buffer table", "Shortest School Name", each #"fx - Shortest School Name"(#"aux - buffer table", [ID]), Text.Type),
#"Filtered Shortest School Name" = Table.SelectRows(#"Invoked fx - Shortest School Name", each [Name] = [Shortest School Name]),
#"Removed Aux Columns" = Table.RemoveColumns(#"Filtered Shortest School Name",{"Shortest School Name"})
in
#"Removed Aux Columns"

Function - no further action required

(dataTable as table, schoolID as number) =>
let
Source = dataTable,
#"Filtered School ID" = Table.SelectRows(Source, each ([ID] = schoolID)),
#"Inserted Text Length" = Table.AddColumn(#"Filtered School ID", "Length", each Text.Length([Name]), Int64.Type),
#"Filtered shortest length" = Table.SelectRows(#"Inserted Text Length", each [Length] = List.Min({#"Inserted Text Length"[Length]}{0})),
Result = #"Filtered shortest length"[Name]{0}
in
Result

Cheers,

Flavio

 @flaviomeneses