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. T...
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])

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?

      • MBreden's avatar
        MBreden
        Helper I
        4 years ago

        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