Forum Discussion
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...
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
I usually just load the data in then create a measure on count - distinct count.
Measure = COUNT('Table'[Column1]) - DISTINCTCOUNT('Table'[Column1])
Or to see the count of duplicates using multiple columns, concatenate them and create the same measure.
I figure since query editor is based on the top 1000, if you're using a large dataset it won't give you an accurate count in power query past the top 1000. But I would like if my assumptions are correct, what other people do, and if there's an easier way as well.
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