How to Remove Duplicates Using Power Query in Excel
Power Query is a simple and reliable way to remove duplicate rows in Excel. It helps you clean data and keep only the unique records you need for analysis.
Table of Contents
Why remove duplicates?
Duplicate rows can cause wrong totals, repeated records and messy reports. Removing them makes your data cleaner and easier to work with. It also helps improve the quality of charts, summaries and pivot tables.
Open your data in Power Query
- Select your data table in Excel.
- Go to the Data tab.
- Click From Table/Range.
- Excel will open the data in Power Query Editor.
Remove duplicate rows
Once your data is in Power Query Editor, you can remove duplicates in a few clicks.
- Select the column or columns you want to use for duplicate detection.
- Go to the Home tab.
- In the Reduce Rows group, click Remove Rows.
- Choose Remove Duplicates.
Choose the right columns
You can remove duplicates based on one column or several columns. If you select only one column, Power Query removes rows where that column repeats. If you select all columns, Power Query removes only rows that are exactly the same in every field.
Example
If you have customer names in one column, you can remove repeated names. If you select the full table, Power Query removes only rows that match completely.
Load the cleaned data back to Excel
After removing duplicates, return the cleaned data to Excel.
- Go to the Home tab.
- Click Close & Load.
- Excel will place the cleaned table into a worksheet.
Refresh the query later
If your source data changes, you do not need to repeat everything manually. You can refresh the Power Query table and Excel will run the duplicate removal steps again.
Tips for better results
- Select all columns if you want to remove full duplicate rows.
- Select only key columns if you want to remove duplicates based on one field.
- Sort your data first if you want to keep a specific row.
- Refresh the query after you update the source data.
