Forum Discussion

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

 

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

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

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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

    • MBreden's avatar
      MBreden
      Helper I
      4 years ago

      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's avatar
        wdx223_Daniel
        Community Champion
        4 years ago

        this code only feedback two columns.

        how about the table have three or more columns?

  • FlavioMeneses's avatar
    FlavioMeneses
    Advocate II
    4 years ago

    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

     

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    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"
  • rbreneman's avatar
    rbreneman
    Helper II
    4 years ago

    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!