Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Eliminating duplicates rows in table

My table has the folowwing data. I only want the row where Col2 has the max value. Is there a method to do this at the Table view level? If not what would be an alternative mehtod?   Col1    Col2 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    Please try this code in power query advanced editor,

    let

    Source = #table(
        {"Col1", "Col2"}, 
        {
            {1, 1},
            {1, 2},
            {1, 3},
            {2, 2},
            {2, 3},
            {2, 4},
            {3, 5},
            {3, 4},
            {3, 3}
        }
    ),
   NewTable = Table.Group(
        Source, 
        {"Col1"}, 
        {{"Col2", each List.Max([Col2])}}
    )
in
    NewTable

     

    Best Regards,

    Bof

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous ,

 

Would you like to group the data by Col1, keeping only the row with the highest value in Col ?

 

Thanks for dharmendars007's concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

 

1\My table(MyTable)

 

2\Create a new table 

NewTable = SUMMARIZE(MyTable,MyTable[Col1],"Col2",MAX(MyTable[Col2]))

 

Best Regards,

Bof

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Bof, This seems to work. However my existing table is very large so I don't want to duplicate it. Is there a way I can use this summarize function in conjuction with my existing table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous ,

       

      Please try this code in power query advanced editor,

      let

    Source = #table(
        {"Col1", "Col2"}, 
        {
            {1, 1},
            {1, 2},
            {1, 3},
            {2, 2},
            {2, 3},
            {2, 4},
            {3, 5},
            {3, 4},
            {3, 3}
        }
    ),
   NewTable = Table.Group(
        Source, 
        {"Col1"}, 
        {{"Col2", each List.Max([Col2])}}
    )
in
    NewTable

       

      Best Regards,

      Bof