Power Query Unique Values: Remove Duplicates or Keep One?

Formulas & functions · Updated November 11, 2026

To get unique values in Power Query in Excel for Microsoft 365, select the column or columns that define a value, then choose Home > Remove Rows > Remove Duplicates. This keeps one row for each distinct selection; if you mean values that occur exactly once, group the data, count occurrences, and keep groups with a count of 1.

What “unique” means in Power Query

People use “unique values” to mean two different things. Power Query’s Remove Duplicates command returns distinct values: it removes repeated copies and leaves one representative of each value or combination. For example, a customer column containing Ava, Ben, Ava becomes Ava and Ben. It does not say that Ava appeared only once.

A value that appears exactly once is sometimes called a singleton. In the same example, Ben is the only singleton; Ava is excluded because she appeared twice. This difference matters for tasks such as finding one-time customers, products listed only once, or IDs that do not have a matching repeat. Power Query can do either job, but the steps are different.

Remove duplicate values in the Power Query Editor

For a worksheet range, first make the source an Excel table if it is not one already: select the data and choose Insert > Table. Click inside the table, then choose Data > From Table/Range to open the Power Query Editor. If a query already exists, open it from Data > Queries & Connections.

  1. In the Editor, select the column whose distinct values you need. To define distinct records by a combination of fields, select all the relevant columns. Hold Ctrl while selecting nonadjacent columns on Windows.
  2. Choose Home > Remove Rows > Remove Duplicates. You can also right-click a selected column header and choose Remove Duplicates.
  3. Check the preview to confirm that the remaining rows represent the values or combinations you intended to keep.
  4. Choose Home > Close & Load to return the result to Excel, or use the load options if you want a different destination.

Power Query records the operation as an Applied Step, so you can revisit it in the Query Settings pane, change the selected columns, or remove the step. The source data is not edited by this operation: the query creates a transformed result. Refresh the query to apply its steps to updated source data.

Choose the columns that define a duplicate

The selected columns determine what Power Query treats as a duplicate. If you select only Customer, rows with the same customer are duplicates even if their dates or order numbers differ. If you select Customer and Product, only repeated customer-product pairs are removed. A customer can therefore appear more than once if the associated products differ.

This selection also affects what happens to other columns. Suppose the table has Customer, OrderDate, and Amount, but you remove duplicates using Customer alone. Power Query retains one row for each customer, including one row’s date and amount. Those other fields are not combined or averaged, and you should not assume that the retained row is the newest or largest. If your goal is one total per customer, use a grouping and aggregation instead of removing duplicates.

Before applying the command, ask: “What columns make two records the same for this purpose?” For a contact list, it might be an email address; for transactions, it could be invoice number and line number. Choosing a label such as Customer Name when a stable customer ID is available can also merge distinct people who happen to share a name.

Distinct values versus values occurring exactly once

To find values appearing exactly once, use Group By to count each value, then filter the count. In the Editor, select the column, choose Home > Group By, and configure the dialog to group by that column with a new column such as Occurrences and the operation Count Rows. Filter Occurrences to equal 1. The result contains only groups whose value appeared once.

For instance, given the values North, South, North, West, the distinct result is North, South, West. The exactly-once result is South and West. The Group By result also makes the frequency visible, which can help verify that your query is doing what you intended. If you need the full original records for singleton values rather than just the value and its count, you can use Group By’s All Rows option and expand or otherwise select the original rows after filtering.

In a text-based M query, Table.Distinct performs the distinct-row operation. For example, if the preceding step is named Source and the key column is Customer, the expression is Table.Distinct(Source, {"Customer"}). The second argument names the columns that determine distinctness. You can inspect or edit a query’s M code through Home > Advanced Editor, but the ribbon command is often easier for a first pass.

Handling blanks, spaces, case, and data types

Unexpected duplicates often come from inconsistent source values rather than a faulty deduplication step. A cell that looks blank may contain a space; “North” and “North ” can be different text values. Likewise, numeric 12 and text “12” are different types, even though they may look alike in a preview. Set appropriate data types and clean or standardize the key before removing duplicates.

  • Trim spaces: select a text column and use Transform > Format > Trim to remove leading and trailing spaces.
  • Standardize text: use Transform > Format options such as lowercase or uppercase if capitalization should not distinguish values.
  • Check nulls and blanks: decide whether missing values should count as one shared value, be excluded, or be corrected at the source.
  • Set data types: use the type icon in the column header or the Transform > Data Type command to make keys consistent.

Text comparisons can be sensitive to case in Power Query, so do not assume that values differing only in capitalization will collapse into one group. If your business rule treats case as irrelevant, normalize the text first, for example by converting it all to lowercase. Always check representative examples in the preview, especially when data comes from multiple systems.

Which duplicate row does Power Query keep?

When the selected key repeats but other columns differ, removing duplicates leaves one representative row. The command is not a rule for “keep the latest,” “keep the first entered,” or “keep the row with the highest amount.” Query optimization and the data source can affect which row survives, so sorting the table before removing duplicates should not be treated as a dependable way to choose a particular record.

If you need a specific row, state that rule explicitly. For example, group records by customer and identify the greatest date within each group, or sort and select a row inside each grouped table using a method suited to the query. Then verify the result, including ties on the date. This is a different task from simply obtaining a list of distinct customer names.

Power Query versus Excel’s UNIQUE function

Excel’s UNIQUE worksheet function can return a distinct list directly in a worksheet. In Microsoft 365 or Excel 2021 and later, a range in A2:A100 can be supplied as =UNIQUE(A2:A100); the result spills into cells below the formula. This is convenient for a live worksheet list and requires no query refresh step.

Power Query is more useful when the result is part of a repeatable data-cleaning process: it can combine sources, change types, normalize text, remove duplicates, and load a refreshed table. The worksheet function returns a list from a range, while Power Query stores transformation steps and can handle a broader import-and-clean workflow. Choose based on where the source lives and how you want updates to work, not on the idea that one method is universally more correct.

Refresh and platform notes

After loading a query, edits to its source do not necessarily change the output immediately. Use Data > Refresh All, or right-click the query’s output and choose Refresh. If the source is an Excel table, adding rows to that table is generally easier to maintain than relying on a fixed cell range; the query still needs to refresh to show the changed data.

These instructions target Excel for Microsoft 365 on Windows. Power Query is also available in supported Microsoft 365 versions of Excel for Mac, but feature availability and the Editor’s interface can vary by build. Excel for the web has more limited query-authoring capabilities than desktop Excel. If a command is missing, check the installed Excel version and platform rather than assuming the same Editor features are available everywhere.

Frequently Asked Questions

Does Remove Duplicates keep one copy or remove every repeated value?
It keeps one representative of each distinct selection and removes the other copies. To exclude every value that occurs more than once, use Group By, count the rows, and filter to a count of 1.

Can I remove duplicates based on just one column?
Yes. Select that column before choosing Home > Remove Rows > Remove Duplicates. Other columns may differ, but only the selected column determines which rows are treated as duplicates.

How do I get unique combinations of two columns?
Select both columns before removing duplicates. Power Query then keeps one row for each distinct pair, such as each Customer and Product combination.

Will Power Query change my original Excel data?
No. The query produces a transformed result and leaves the source table or range intact. Refresh the query to rebuild its output from the current source.

Why do values that look identical remain separate?
They may differ by spaces, capitalization, hidden characters, or data type. Trim and standardize text, inspect nulls, and make key-column types consistent before deduplicating.

Can I keep the newest row for each value?
Not reliably by sorting and then removing duplicates alone. Define the latest-date rule explicitly within each group, and decide how to handle records tied for the newest date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.