Remove duplicate and control which row stays

[rbreneman]

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. The screenshot below illustrates this. Is it possible to do this in Power Query Editor? I'm not interested in DAX. Thanks!

[rbreneman]

Thank you all for your proposed solutions! I decided to go with @wdx223_Daniel's solution as it was the simplest to implement. It works perfectly!

Thanks again!

[wdx223_Daniel · Accepted solution]

NewStep=Table.FromRecords(Table.Group(PreviousStepName,"ColumnB",{"n",each Table.Min(_,each Text.Length([ColumnA]))})[n])

[MBreden]

Hi Daniel,

very nice solution!
I played around a bit and this small step surprisingly also gives the right result:

[Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MjZRitUBclB4Tk5AHpBjCuEhc5yBHCDbDMJB4bm4uAC5QJ45hAvnxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t]) ,

    Result = Table.Group(Source, {"ID"}, {"Name", each List.Min([Name])})
][Result]

[wdx223_Daniel]

this code only feedback two columns.

how about the table have three or more columns?

[MBreden]

I have only tested it with the sample data.
I was just very surprised that List.Min([Name]) is evaluated.
Can you explain this?

Greetings Mel

[ronrsnfld]

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"

[FlavioMeneses]

Hi, MS say "there's no guarantee that the first instance in a set of duplicates will be chosen when duplicates ar..." 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