Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Remove Duplicates - Row count

I know how to remove the duplicate rows in the Query Editor. Is there a way to know how many duplcates were present?   For example, in excel when we remove duplicates, it tells us "This many duplic...
Need Help
Show and Tell
  • ibarrau's avatar
    ibarrau
    7 years ago

    You can check all rows in power query. The top 1000 is only if the result has more than 1000. If you run "Keep duplicates" first then you can add a custom step like this to check it

    = Table.RowCount(#"Keep duplicates")

    Hope this helps

ibarrau's avatar
ibarrau
Super User
7 years ago

Hi, yes you can. First select the columns you want to check for duplicates. Then keep duplicates:

keep duplicates

Then you can group by and count if there are a lot of rows.

After you are sure of what the data is showing you can remove duplicates if you are sure.

 

Another way is to create a DAX table or connnect with DAX Studio and write a query like this:

EVALUATE
FILTER (
    ADDCOLUMNS (
        SUMMARIZE ( muestradatos, muestradatos[Afiliado] ),
        "CantDuplicados", CALCULATE ( COUNTROWS ( muestradatos ) )
    ),
    [CantDuplicados] > 1
)

Column1 should be the field you want to check for duplicates. You can add more columns or check for all. The result of that query should be which columns have duplicates and a count of them.

 

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.