Remove Duplicates in Power Query: A Practical Guide

Yes. In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, select the column or columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Power Query keeps one row for each distinct selected value or combination and removes the others when the query is refreshed or loaded.

What removing duplicates means in Power Query

Power Query treats duplicate removal as a step in a data-preparation process, not as a command that permanently edits the original worksheet or database. The step changes the result produced by the query. Your source data stays as it was, and the query applies the same rule again when it refreshes.

The columns you select define what counts as a duplicate. If you select just Customer ID, rows with the same ID are duplicates even if their names, dates, or order amounts differ. If you select Customer ID and Order Date, only rows matching in both selected columns are duplicates. Other columns do not determine whether rows match, though their values may differ among the rows that are removed.

This distinction is important: removing duplicate rows is not always the same as removing repeated records. A customer can have many legitimate orders, so deduplicating only by customer ID could discard valid data. Before applying the step, decide what one unique record represents in your dataset: a person, an invoice, a transaction, or perhaps a combination of fields.

How to remove duplicates step by step

For a table already connected to Power Query in Excel for Microsoft 365 on Windows, use this workflow:

Open the Data tab and choose Queries & Connections to show the workbook’s queries.
Right-click the query you want to change and choose Edit. This opens Power Query Editor.
In the preview, select the column headings that define a unique record. Hold Ctrl while selecting non-adjacent columns on Windows.
Choose Home > Remove Rows > Remove Duplicates. You can also right-click a selected column heading and choose Remove Duplicates.
Check the preview and the Applied Steps pane. If the result is right, choose Home > Close & Load to return the result to Excel.

If you are creating a query from a worksheet table, select a cell in the table and choose Data > From Table/Range first. After you remove duplicates and load the result, use Data > Refresh All to run the query again against its source. Excel for Mac also supports Power Query, but available connectors and some interface details can differ by Microsoft 365 build; use the equivalent query-editing commands shown in your version.

Choose the right columns before deduplicating

The most consequential choice is the key: the field or set of fields that identifies one record. A useful way to make that choice is to consider the rule you would use to decide whether two records refer to the same real-world item.

One identifier: Use an Order ID if each order should appear once and the ID is reliable and unique.
A combination of fields: Use Product ID and Warehouse if a product should appear once per warehouse, but can appear in several warehouses.
All columns: Select every column when you only want to remove rows that are identical across the whole table. Rows with any different value will remain.

For example, consider rows with Order ID 8401, Customer “Lee,” and amounts of $35 and $53. Removing duplicates by Order ID keeps only one of these rows, even though the amounts differ. Removing duplicates using every column keeps both, because the rows are not identical. Neither choice is inherently correct; the intended meaning of the data decides.

Be careful with fields that look unique but are not guaranteed to be. Names, email addresses, and dates can be repeated or entered inconsistently. If a key contains leading or trailing spaces, inconsistent capitalization, or different data types, values that look alike may not behave as expected. Clean or standardize those fields before deciding that the records are duplicates.

Which duplicate row does Power Query keep?

Removing duplicates guarantees a distinct result according to the selected columns, but it is not a safe way to specify which version of a conflicting record should survive. If duplicate keys have different values in other columns, do not assume that Power Query will always keep the first row you see in the preview, the newest row, or the row with the largest amount. Query optimizations and data-source processing can affect which matching row remains.

This matters when a customer or product record has multiple versions. Suppose a customer ID appears twice, once with an old address and once with a current address. Removing duplicates by customer ID alone does not express “keep the current address.” Sorting by date and then removing duplicates may look like a solution, but sorting alone is not a reliable way to guarantee which duplicate survives every refresh or query plan.

Instead, define the selection rule explicitly. If you need the latest date per customer and only need the date as the result, use Group By on Customer ID and aggregate the date using Max. If you need the entire row associated with the latest date, group records by the key and deliberately select the row with the greatest date within each group. Resolve ties with another rule, such as a unique update timestamp or record ID. This is more precise than treating duplicate removal as a “keep newest” command.

What happens when the query refreshes?

Power Query records each transformation in the query, including the duplicate-removal step. On refresh, it obtains the available source data and applies the steps in sequence. If new rows arrive with an already-seen key, the refreshed result will again contain one row for that key under the same rule. If the source changes, the surviving non-key values may also change, which is another reason not to rely on an unspecified choice among conflicting records.

The cleaned result is separate from the source. Loading it to a worksheet creates or updates an output table; it does not delete duplicate records from the original worksheet table, CSV file, or database. This separation is useful because the raw data remains available for auditing and because the transformation can be revised later. If you need to physically delete records from a source system, that is a separate operation and should be performed with the appropriate source-system tools and safeguards.

Power Query may also perform some transformations at the data source when query folding is available. Folding can improve efficiency, but it is another reason to treat the query as a transformation rather than a manual, row-by-row deletion. The output is the result of the query’s rules, not a guarantee about which physical source record was removed.

Common problems and how to avoid them

Too few columns selected: The query removes records that share the selected key even when other fields differ. Revisit the key, or use a deliberate grouping rule if differences need to be resolved.
Too many columns selected: Minor differences, such as a note or timestamp, make rows distinct, so apparent duplicates remain. Select only the fields that define record identity.
Hidden spacing or inconsistent text: “North” and “North ” may not match as intended. Apply text-cleaning steps such as trimming whitespace before deduplicating, and standardize capitalization if your business rule treats it as irrelevant.
Unexpected blanks or nulls: Several records with missing key values can be treated as duplicates under the selected columns. Decide whether blank keys should be retained, repaired, or filtered out rather than letting them collapse accidentally.
Different data types: A number stored as text may not behave like the same value stored as a number. Set the column’s data type deliberately before comparing records.

Text comparison behavior can depend on the operations and source involved. If “abc” and “ABC” must count as the same key—or must count as different—make that rule explicit by normalizing the text or using a suitable comparison approach, then inspect the preview with representative examples. Do not assume that visual similarity alone proves two values will be treated as identical.

Remove duplicates versus other Power Query options

Remove Duplicates is best when the goal is simply one output row per selected key and the particular non-key values retained do not matter. It is concise and easy to maintain. Group By is a better fit when you need a defined summary, such as a count of records per customer, the maximum transaction date, or the sum of sales by product.

If the requirement is “keep the complete latest record,” use grouping with an explicit within-group selection rule rather than a plain duplicate-removal step. If you only want to inspect repeated keys without changing the result, group by the key and count rows, or create a separate query for review. That makes it possible to identify data-quality problems before deciding which records to consolidate.

Excel’s worksheet Data > Remove Duplicates command is different: it removes rows directly from the selected worksheet range and can be undone immediately with Ctrl+Z, until other actions intervene. Power Query instead stores a repeatable transformation and leaves its source unchanged. Choose the worksheet command for a one-time edit to a range; choose Power Query when you want a refreshable cleaning process.

Frequently Asked Questions

Does Power Query remove duplicates from the original data?
No. It removes them from the query output. The original worksheet, file, or database is not altered by this transformation.

Can I remove duplicates based on multiple columns?
Yes. Select all columns that together define a unique record, then choose Home > Remove Rows > Remove Duplicates. A row is considered a duplicate when its values match the selected-column combination.

Can I keep the newest duplicate in Power Query?
Not reliably with the basic Remove Duplicates command alone. Use Group By and explicitly select the row with the latest date, including a tie-break rule if two records have the same date.

Will new duplicates be removed after a refresh?
Yes. The query reapplies the deduplication step to the refreshed source data, using the same selected columns and transformation sequence.

Why are duplicates still appearing?
They may differ in a selected column because of spaces, capitalization, data types, or other small variations. Check the chosen columns and standardize values before removing duplicates.

Can I undo a Power Query duplicate-removal step?
Yes. In Power Query Editor, remove the step from the Applied Steps pane or edit the query. This changes the query transformation; it does not restore or modify source data because the source was not deleted.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.