Identify Duplicate Values in a Table with Power Query

This video covers how to identify duplicates with Power Query. Usually finding duplicates can be done with other Excel features like Conditional Formatting, but if you had a large data set (like hundreds of thousands of rows), you may find Excel slow. Power Query can handle large data sets so it's perfect for this scenario.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[lawrenceliew]

Thanks, Doug that's what I was looking for!

[FRANKWHITE]

Thats exactly what i needed today! Thanks

[ElWiwif]

Thanks buddy! Your video still rocks!!

[shoppersdream]

This was really nice. Can I split this workbook into 2 worksheets with PowerQuery? One with Duplicates and the other No Duplicates? Thanks

[michaelrodriguez]

Nice video sir. Great job explaining and giving the extra tips along the way !

[InglesallYou]

How to compare two big excel files by power query? Sheet1.xls and Sheet2.xls Both haveThousand rows and more than 50 columns each . they have complete different columns unless 2 coluns C end D are common in both. .I want to have sheet 3 with all lines and coluns of sheet1 except all lines( when the line C and D lines) matchs sheet 2 ? Pls.
Basically i want extract the difference
Thanks

[olaayorinde]

Hi DougThanks for this video. How can I not only identify duplicate but remove them using power query. I was trying to use COUNTIF function to label first occurrence as 0 and other will follow as 1, 2, 3...Is there a power query formula to the duplicate in number of occurrence the way countif fn will count them in excel sheet. Thanks

[markhealy]

Can it be done for multiple columns, I'd be working with sales files where one column will always have duplicates such as invoice numbers. I wouldn't be removing those. I would like to remove a row of 20 columns where the entire row is an obvious duplicate that must be removed.

[SketCr]

Hi Dough! If I wanted to flag the duplicates in Power Query. Say a calculated column, the first line would have a 1 and all duplicates below it should show a 0. Is this possible in PQ?

[arjunmv]

How to do look up in power query for large data sir.. Data exceeded excel maximum limit

[wadieseruge]

hi Doug,
if I want cell a2 change it's cell color, every time cell a1, shows the word N/A. what formula would I need to use ? thank you. dan

[ManishKumar-qjsq]

how to find partial duplicate in power query

[JJ_TheGreat]

Why not just go to "Keep Rows" > "Keep Duplicates" - to identify them... Easy peasy. Then you can always X out the step it creates after identifying them.