To delete duplicates with Power Query in Excel for Microsoft 365 on Windows, open the query, select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Select every column if you mean identical entire rows; select only key columns if records with the same key should count as duplicates.
What Power Query does when it removes duplicates
Power Query does not erase duplicate records from the original worksheet or source file. It adds a transformation to the query, and the query output contains one row for each distinct combination of the selected columns. The source remains available, and refreshing the query reapplies the transformation to the latest source data.
This is useful when data is imported repeatedly or needs the same cleanup each time. For example, a monthly sales export may contain repeated lines. Rather than manually deleting them after every download, you can create a query that removes duplicates and refresh it when the new file arrives.
The important decision is what “duplicate” means for your task. Two rows can be duplicates because every field matches, or because a particular identifier matches even though other fields differ. Power Query follows the columns you select, so choosing the wrong set can remove records you intended to keep.
How Power Query decides which rows match
When you select one column, Power Query compares values in that column. When you select several columns, it compares the combined values in those columns. For example, selecting Customer ID and Invoice Number treats two rows as duplicates only when both fields match. Differences in unselected fields do not prevent a match.
Suppose a table contains Customer ID, Name, City, and Status. If you select all four columns, two rows with the same ID and name but different status remain separate. If you select only Customer ID, those rows count as duplicates, and the query returns one of them. That distinction can change totals and downstream reports, so decide the matching rule before applying the command.
Text formatting can also affect the result. Extra spaces or inconsistent capitalization may make values look the same to a person while Power Query treats them as different values. If those differences should not matter, normalize the relevant text columns first, for example by trimming spaces or changing text to a consistent case, then remove duplicates.
Steps to remove duplicates in Excel
- Bring the data into Power Query. If the data is in a worksheet range, select a cell in it and choose Data > From Table/Range. Confirm the range and whether it has headers, then select OK. Excel opens the Power Query Editor.
- Choose the duplicate-defining columns. Click a column header to use one field. To select multiple headers, hold Ctrl on Windows while clicking each one. To select a continuous group, click the first header and Shift-click the last. For identical whole rows, select all relevant columns.
- Remove the duplicates. On the editor ribbon, choose Home > Remove Rows > Remove Duplicates. You can also right-click a selected column header and choose Remove Duplicates.
- Check the preview. Confirm that the row count and remaining values make sense, especially if you selected only a key field. The preview shows the query result, not a deletion from the original worksheet.
- Load the result. Choose Home > Close & Load to return the result to Excel. Depending on your needs, you can use the Close & Load options to choose where the query output goes.
If you already have a query, select a cell in its output and choose Data > Queries & Connections, then open the query for editing. The exact entry point can vary with the workbook layout, but the duplicate-removal command is in the Power Query Editor.
Choose the right columns before removing rows
Use all columns when your goal is to remove only rows whose every field is identical. This is a conservative choice: records that share an ID but contain different dates or amounts are kept because the full rows differ.
Use a subset when one or more fields define uniqueness in the real-world data. For instance, if a product catalog should have one row per SKU, select SKU. If a contact list should have one entry per email address, select Email. If a transaction is uniquely identified by both account and transaction number, select both fields. A single field is not always a sufficient key; two people may share a name, and a customer may have several legitimate orders.
Before removing records based on a key, look for blank or malformed key values. If several rows have an empty email address, removing duplicates by Email could collapse those separate people into one row. Filter or otherwise handle missing identifiers according to the purpose of the dataset rather than assuming blanks represent the same entity.
Which duplicate is kept?
Removing duplicates leaves one row for each distinct selected value or combination, but it is risky to rely on this command to retain a particular record when unselected fields differ. You might want the newest address, the latest status, or the row with the most complete information. The basic remove-duplicates step does not express that business rule.
For example, imagine two rows have the same Customer ID but different Update Date values. If you deduplicate using only Customer ID, one record remains, but you should not assume that it will always be the row with the newest date. Query optimizations and the source connection can affect which matching row is retained.
For a dependable “keep the latest” result, define the rule explicitly. One approach is to group by the customer key and calculate the maximum update date, then merge that result back to the original rows using both the key and date. If multiple rows share the maximum date, add a tie-break rule, such as a unique record ID. Sorting before removing duplicates may look like a shortcut, but it is not a reliable guarantee of which record survives.
Refresh behavior and the original data
Power Query records the duplicate-removal step in the query’s applied steps. When the source changes, refreshing the query runs that step again. In Excel, use Data > Refresh All to refresh workbook connections, or use the refresh command for an individual query where available.
The output table is a result, not the source of truth. Editing a value directly in a loaded query table is generally not a way to change the underlying source or the query logic; a refresh can replace the output with the newly generated result. Make corrections in the source data or add an appropriate transformation in Power Query.
Keep the source intact if you may need to audit or recover removed records. If the original source is itself a worksheet table, the query can refer to that table while loading a cleaned result elsewhere. Avoid loading the cleaned output over the source table, which can create confusion about which data is original and which is transformed.
Common problems and how to fix them
- Rows that look identical are not removed. Check for leading or trailing spaces, nonprinting characters, different data types, or differences in a column you selected. Apply appropriate cleanup to the relevant columns and inspect the values in the preview.
- Too many rows disappear. You may have selected only a field that is not unique, such as a city or last name. Undo the step in the Applied Steps pane or delete that step, then choose a more suitable key or select all columns.
- One record per key remains, but it is the wrong one. This is a survivor-selection problem. Specify a rule such as latest date or highest priority and build it into the query, rather than depending on the basic duplicate-removal step.
- Refresh brings back records you removed manually. Manual deletion from the loaded output does not change the source or query. Add the transformation in the query editor so it runs again on refresh.
- Blank identifiers merge unrelated records. Decide how missing keys should be treated. Filter blank keys out of the deduplication step or retain them separately if they represent records that cannot be matched safely.
Power Query comparisons can be sensitive to text differences, including case, depending on the operation and comparison behavior involved. If uppercase and lowercase forms should count as the same key, normalize the column to a consistent case before deduplicating and verify the resulting preview. Do not assume that visually similar text is identical.
Power Query compared with Excel’s Remove Duplicates
Excel’s worksheet command, Data > Remove Duplicates, removes matching records directly from the selected range or table. It is handy for a one-time cleanup, but the change is made to that worksheet data, so keep a copy if you may need the original rows.
Power Query is better suited to repeatable cleanup. It preserves the source and stores the transformation as a query step, which can be refreshed when new source data arrives. The trade-off is that the cleaned result is a query output that must be loaded and refreshed, rather than a direct edit to the original table.
Excel for Mac also includes Power Query capabilities, but available connectors and parts of the interface can differ by version. Excel for the web has different Power Query authoring and refresh capabilities from the Windows desktop app. If a command or connector is missing, check the options available in your Excel version instead of assuming every platform has an identical ribbon.
Frequently Asked Questions
Does Power Query delete duplicates from my original Excel table?
No. It removes duplicates from the query result. The original source data remains unchanged unless you separately edit or replace it.
How do I remove duplicates based on just one column?
In Power Query Editor, select that column’s header and choose Home > Remove Rows > Remove Duplicates. The output keeps one row for each distinct value in that column.
How do I remove only rows that are exact duplicates?
Select all columns that define the complete row before using Remove Duplicates. If you leave out a column, differences in that field will not prevent rows from being treated as duplicates.
Can I keep the newest row for each ID?
Do not rely on Remove Duplicates alone to choose the newest record. Group by the ID, identify the maximum date, and match that result back to the source rows; decide how to handle ties.
Will Power Query remove duplicates again after I add new data?
Yes, when you refresh the query, its removal step runs on the refreshed source. New rows that match existing selected values will be reduced to one result for each distinct value or combination.
Can I undo a Power Query duplicate-removal step?
In the editor, remove or change the corresponding step in the Applied Steps list. Because the source is not deleted by the query transformation, you can also change the query and refresh to rebuild the output.
Related
- Excel Number Format: Red Negative Numbers in Brackets
- Excel Number Format for Negative Numbers in Parentheses
- Excel Number Format for Lakhs With Commas
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Print Shortcut: How to Print a WorksheetIn Excel for Microsoft 365 on Windows, press Ctrl+P to open the Print screen, review the preview and…
- How to Edit Checkboxes in Google SheetsTo edit a checkbox in Google Sheets, select its cell and open Data > Data validation. Edit the…
- Inventory Spreadsheet: Simple Setup and Useful TipsYes—an inventory spreadsheet can be simple: list each item once, record its quantity and reorder point, and update…
- How to Insert a Dividing Line in WordTo insert a dividing line in Word, place the cursor where the line should go and type three…
- Data Validation: Definition, Rules, and ExamplesData validation is the process of checking whether data meets defined requirements before it is accepted, stored, or…
- Excel Table with Subcategories: Setup and ExamplesYes. To make an Excel table with subcategories, put the main category and its subcategory in separate columns,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.