Deleting all duplicate rows but keeping one

When you work with Power Bi or Power Pivot duplicates are not allowed but due to different problems or error I know there are some duplicates in this table.

I need to eliminate the extra rows from the perspective of the key columns. Some other columns may have slightly different data but I do not care about that. I still need to keep one of these rows however.

SELECT DISTINCT won’t work because it operates on all columns and I need to suppress duplicates based on the key columns.

How can I delete the extra rows but still keep one efficiently?

It’s very simple you have only to use this query:

WITH cte AS (
  SELECT[KEY1], [KEY2], 
     row_number() OVER(PARTITION BY KEY2, KEY1 ORDER BY KEY1) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1


With this simple query you can delete al duplicate row keeping one. You can use order by to keeping for example the last raw (order by date asc).

Create a dynamic (automatically updated) Excel dropdown

If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.

The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table. If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.

  1. You start by creating a usual dropdown based on a named range as described above.
  2. In step 2, when creating a name, you put the following formula in the Refers to box.=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    Where:

    • Sheet1 – the sheet’s name
    • A – the column where the items of your drop-down list are located
    • $A$1 – the cell containing the first item of the list

As you see, the formula is comprised of 2 Excel functions – OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
Creating a dynamic drop-down list in Excel using the OFFSET formula

 

The main advantage of dynamic drop-down lists is that you won’t have to change the reference to the named range every time after editing the source list. You simply delete or type new entries in the source list and all of the cells containing this Data Validation list will get updated automatically!