Power Query Keep Duplicates: How the Command Works

Yes. Power Query’s Keep Duplicates command keeps every row whose value—or combination of values—in the selected columns occurs more than once; it does not keep just one copy. In Excel for Microsoft 365 on Windows, select the columns that define a duplicate, then choose Home > Keep Rows > Keep Duplicates in the Power Query Editor.

What Keep Duplicates means

Power Query treats the selected columns as a key for checking repetition. If you select Customer ID, it checks whether each ID appears more than once. If an ID occurs three times, all three matching rows remain in the result. Rows with IDs that occur only once are excluded.

This is the opposite of the usual goal of Remove Duplicates, which keeps one representative row for each selected key and discards the other matching rows. Keep Duplicates is useful when you want to investigate repeated records, isolate recurring customers or product codes, or retain all transactions associated with a repeated identifier.

For example, if a table contains IDs A17, A17, B04, C22, C22, C22, keeping duplicates using only the ID column returns both A17 rows and all three C22 rows. B04 disappears because it appears once. The result has five rows, not three: the command filters out unique keys but does not collapse the repeated keys into one row.

Choose the columns that define a duplicate

The choice of columns is the most important part of the operation. Power Query checks the values in the selected columns together, row by row. A repeated value in one column is not necessarily a duplicate record if another selected column differs.

Suppose an orders table has Customer ID, Order Date, and Order Number. Selecting Customer ID finds customers with multiple orders. Selecting Customer ID and Order Date finds customers who have more than one order on the same date. Selecting all three columns finds rows that repeat the same customer, date, and order number. These are different questions, so they produce different results.

Before choosing the key, decide what a repeat means in your task:

Repeated identifier: Select the ID column, such as invoice number or employee ID.
Repeated combination: Select the fields that jointly identify a record, such as product code and warehouse.
Entire row repeated: Select every column, so rows qualify only when all selected values match.

Selecting every column can be too strict if records contain changing fields such as an import timestamp, notes, or a status. Those differences will stop two otherwise similar records from matching. Conversely, selecting only a name can group different people who happen to share that name. Use stable identifiers or a deliberate combination of fields whenever possible.

Keep Duplicates in Excel Power Query

In Excel for Microsoft 365 on Windows, a typical workflow is:

Open the query in the Power Query Editor. For an Excel table, you can select a cell in the table and choose Data > From Table/Range; for an existing query, open it from Data > Queries & Connections.
In the preview, select the column header or headers that define a duplicate. To select several separate columns on Windows, hold Ctrl while selecting their headers.
Choose Home > Keep Rows > Keep Duplicates.
Review the preview and the Applied Steps pane. The command adds a transformation step to the query.
When the result is right, choose Home > Close & Load, or Close & Load To, to return the output to Excel.

The exact labels or ribbon layout can vary somewhat by Excel build. In a Mac version, Power Query availability and editor controls can differ from Windows; look for the equivalent Keep Rows command in the editor rather than assuming every ribbon path is identical. Power Query transformations are part of the query, so refreshing the query applies the operation again to the current source data.

What happens to the rows and their order

Keep Duplicates filters the table; it does not summarize repeated groups. If a qualifying key appears four times, all four source rows are kept, including any other columns in those rows. Values in columns you did not select do not determine whether the rows qualify, but they remain in the output.

Do not treat the resulting row order as a reliable way to choose a preferred record. The command is not a rule such as keep the newest, keep the first, or keep the row with the most complete fields. If you need a particular order for presentation, sort the result after the filtering step. If you need to select one preferred record within each duplicate group, define that rule separately—for example, sort by a reliable date and then remove duplicates using the key columns. Power Query operations and data sources do not always guarantee that an earlier sort will determine which row survives a later operation.

Keep Duplicates also does not add a count column. It tells you which rows belong to repeated keys, not whether a key appeared twice or twenty times. Add a count-based step if the number of occurrences matters.

Count occurrences or keep only certain repeats

For a quick way to identify repeated rows, Keep Duplicates is often enough. If you need to distinguish keys that occur exactly twice from those that occur more often, calculate group counts instead. In the editor, select the key columns and use Transform > Group By. Set the operation to Count Rows and give the count column a clear name, such as Occurrences. You can then filter that count to the condition you need.

Grouping changes the shape of the data: the grouped result contains one row per key and a count, not every original detail row. To return the full records with their counts, merge the count result back to the original query using the same key columns, then filter the merged count. Check that the chosen keys identify groups as intended; a mistaken key can create misleading counts.

This distinction matters in common requests:

To keep all records for keys that appear at least twice, use Keep Duplicates.
To see one row per repeated key and its frequency, group by the key and count rows.
To keep only keys occurring exactly twice, use a count and filter the count to 2.
To retain one record per key, use Remove Duplicates or another explicit selection rule—not Keep Duplicates.

Clean values before checking duplicates

Values that look the same on screen may not be identical in the source. Text may contain leading or trailing spaces, nonprinting characters, inconsistent punctuation, or different capitalization. Data types can also matter: a number and text that displays like that number should not be assumed to be the same value. If matching depends on a cleaned version of a field, standardize it before applying Keep Duplicates.

For example, a customer code entered as AB12 in one row and AB12 in another may appear identical at a glance but contain different text. In the Power Query Editor, use Transform > Format > Trim to remove leading and trailing spaces. Use the appropriate text-formatting options to standardize case if upper- and lowercase entries should count as the same key. Set the column to the intended data type before relying on comparisons.

Power Query text comparisons are case-sensitive in M, so do not assume that entries differing only in capitalization will match. If case should not matter for your task, create or transform a normalized key before checking duplicates. Keep the original column if you need to preserve its original presentation in the final output.

Common problems and practical checks

A result with no rows does not necessarily mean the source contains no apparent repeats. It may mean the selected columns form a key that is too specific, a value contains hidden spaces, the data types are inconsistent, or capitalization differs. Try checking a smaller, well-understood key and inspecting the source values before changing the query.

If too many rows remain, the selected key may be too broad in the sense that it contains too few identifying fields. For example, selecting only City will keep all rows for any city entered more than once, even when the records concern different customers. Add fields to the key until it matches the definition of a duplicate you actually need.

Other useful checks include:

Confirm that the preview includes all relevant source rows and that earlier filters have not already removed records.
Inspect the Applied Steps pane to see whether a later step filters out rows that Keep Duplicates retained.
Check null or blank key values. Repeated missing values can be treated as a repeated key, which may not be meaningful in your dataset.
Refresh the query after changing the source, then verify that the output still matches the intended rule.

For large sources, identifying repeated keys can require the source or Power Query to compare many values. Some connectors can translate supported transformations into source-side operations through query folding; others cannot, and folding may stop at a particular step. Performance therefore depends on the data source, connector, query, and volume. If the query becomes slow, check whether earlier steps are reducing unnecessary rows and columns, and use the query-folding indicators or View Native Query when available for that connector and step.

Keep Duplicates versus Remove Duplicates

The commands sound similar, but they answer opposite questions. Keep Duplicates retains every row in a group with more than one occurrence and excludes one-off groups. Remove Duplicates retains one row for each selected key and removes additional rows with that key. Neither command automatically decides which record is most accurate or most recent.

For a list of email addresses where you want to find addresses used by multiple records, Keep Duplicates on the email column is appropriate. For a clean mailing list containing just one row per email address, Remove Duplicates is closer to the goal. If several rows share an email but have different dates or contact details, decide which row should represent the address before removing extras.

Frequently Asked Questions

Does Keep Duplicates keep every copy?
Yes. When a selected key appears more than once, the result keeps all rows with that key. It does not reduce the group to one row.

Can I keep duplicates based on just one column?
Yes. Select that column before choosing Keep Duplicates. All rows whose value in that column repeats will remain, even if other columns differ.

How do I keep only exact duplicate rows?
Select every column that should define an identical row, then choose Keep Duplicates. Any difference in a selected value means the rows are not an exact match under that key.

Can Power Query show how many times each value occurs?
Yes. Use Transform > Group By on the key columns and choose Count Rows. Keep Duplicates alone filters repeated rows but does not display an occurrence count.

Will Keep Duplicates choose the newest record?
No. It retains all rows in a repeated group and does not select a preferred one. To keep the newest record, establish a reliable date-based selection rule and check the result rather than relying on Keep Duplicates.

Does the result update when the source changes?
Yes, when you refresh the query, Power Query reapplies its steps to the source data it reads. The output can change if keys gain or lose repeated rows, or if upstream filters and transformations change.

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.