How to use power query to remove duplicates

Answer

To remove duplicates in Excel using Power Query, follow these steps carefully:

Step 1: Load Your Data into Power Query

1. Select your data range or table in Excel.

2. Go to the Data tab.

3. Click Get & Transform Data → From Table/Range.

4. Ensure your data has headers, then click OK.
   Your data will open in the Power Query Editor.

Step 2: Identify Columns to Check for Duplicates

Decide which columns should be considered for duplicates. For example:

• If duplicates are based on the entire row, select all columns.

• If duplicates are based on one specific column, select only that column.

Step 3: Remove Duplicates

1. In the Power Query Editor, select the column(s) that determine duplicates.

2. Go to the Home tab in the editor.

3. Click Remove Rows → Remove Duplicates.
   Power Query will retain only the first occurrence of each duplicate and remove the rest.

Step 4: Load the Cleaned Data Back to Excel

1. Click Home → Close & Load.

2. The data will be loaded back into Excel as a new table with duplicates removed.

Notes and Tips

• Power Query removes duplicates dynamically, which means if the source data changes, you can refresh the query to update the table.

• You can also combine this with other transformations like filtering or sorting before removing duplicates.

• This method works identically on Windows and Mac versions of Excel that support Power Query.

Example

Suppose you have a table of customer emails:

After using Power Query to remove duplicates based on the Email column, the table becomes:

This method ensures no repeated emails exist in your dataset.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report