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