Duplicate records silently corrupt your reports and inflate your metrics. This hands-on lesson teaches you every deduplication technique in Power Query — from one-click removal to precise, sort-controlled survivor selection — so your data is clean and your analysis is trustworthy.
Imagine you've just pulled together a customer list from three different sources — your CRM, your email platform, and a spreadsheet your sales team has been maintaining since 2019. You load all three into Power Query, append them together, and you're left with 4,700 rows. Great. Except you only have about 2,000 actual customers. The rest are duplicates: the same people appearing twice, three times, sometimes five times, with slightly different capitalization, different email formats, or just straight-up copy-paste repetition from someone hitting "export" one too many times.
Deduplication — the process of identifying and removing duplicate records — is one of the most common and most consequential data cleaning tasks you'll ever do. Get it wrong and your counts are inflated, your aggregations are wrong, and your reports tell a story that isn't true. Get it right and you have a clean, trustworthy dataset that downstream analysis can actually rely on. Power Query gives you several tools to handle this, from a single button click to precise, surgical M code that lets you control exactly which duplicate gets kept and which gets dropped.
By the end of this lesson, you'll be able to confidently deduplicate data at the whole-row level, at the individual column level, and in situations where you need to make smart choices about which row to keep when duplicates conflict. Here's exactly what we'll cover:
What you'll learn:
This lesson assumes you're comfortable opening Power Query Editor and performing basic transformations. If you're new to Power Query, start with Getting Started with Power Query: Master Connect, Transform, Load for Real-World Data before continuing here. You should also understand what a "step" is in the Applied Steps pane and how Power Query builds a transformation pipeline sequentially.
Before you click anything, you need to answer a question that sounds obvious but genuinely isn't: what makes two rows duplicates in your data?
There are three common interpretations:
CustomerID or Email), but differ in other columns. This is the more common and more dangerous case.Understanding which type you're dealing with completely changes which technique you should use. Let's work through each exact-match scenario with realistic data.
Let's start with the simplest case: every column in the duplicate rows is identical. This happens frequently when you're combining data from multiple files in a folder or appending exports that overlap in date range.
Sample data — Order Log:
| OrderID | CustomerName | Product | Quantity | OrderDate |
|---|---|---|---|---|
| 1001 | Sarah Chen | Laptop | 1 | 2024-01-15 |
| 1002 | Marcus Webb | Monitor | 2 | 2024-01-16 |
| 1001 | Sarah Chen | Laptop | 1 | 2024-01-15 |
| 1003 | Priya Nair | Keyboard | 3 | 2024-01-17 |
| 1002 | Marcus Webb | Monitor | 2 | 2024-01-16 |
Rows 1 and 3 are identical. Rows 2 and 5 are identical. This is a textbook full-row duplicate situation.
To remove these in Power Query:
That's it. Power Query compares every column simultaneously and keeps only the first occurrence of each unique row combination.
Note
"First occurrence" means first in the current sort order of the table, which may not be the original source order unless you've explicitly sorted first. We'll talk about controlling this in the next section.
After applying Remove Duplicates, your result should look like:
| OrderID | CustomerName | Product | Quantity | OrderDate |
|---|---|---|---|---|
| 1001 | Sarah Chen | Laptop | 1 | 2024-01-15 |
| 1002 | Marcus Webb | Monitor | 2 | 2024-01-16 |
| 1003 | Priya Nair | Keyboard | 3 | 2024-01-17 |
In the M code behind the scenes, this step looks like:
= Table.Distinct(#"Previous Step")
Table.Distinct is the function Power Query calls when you remove duplicates from the full table. It returns a new table with only unique rows.
Full-row deduplication is clean and simple, but real data is messier. More often, you have a column that should be unique — a Customer ID, an email address, a product SKU — but it isn't. The other columns on the duplicate rows might differ (one has a phone number, one doesn't; one has "Manager" as the job title, one has "Senior Manager").
Sample data — Customer List:
| CustomerID | Name | Segment | LastUpdated | |
|---|---|---|---|---|
| C-001 | Sarah Chen | sarah@example.com | Enterprise | 2024-03-01 |
| C-002 | Marcus Webb | marcus@example.com | SMB | 2024-01-10 |
| C-001 | Sarah Chen | sarah@example.com | Enterprise | 2024-06-15 |
| C-003 | Priya Nair | priya@example.com | Enterprise | 2024-02-28 |
CustomerID C-001 appears twice. The rows differ only in LastUpdated. This means full-row deduplication won't catch it — the rows aren't identical. You need to deduplicate on the CustomerID column specifically.
To deduplicate on a specific column:
CustomerID to select itOr alternatively: with CustomerID selected, go to the Home tab → Remove Rows → Remove Duplicates.
This tells Power Query: "Within this column, keep only the first occurrence of each unique value — and bring the rest of that row along with it."
The M code for this is:
= Table.Distinct(#"Previous Step", {"CustomerID"})
Notice the second argument: {"CustomerID"} is a list of column names that define uniqueness. This is the key difference from the full-table version.
Deduplicating on multiple columns together:
Sometimes uniqueness is defined by a combination of columns. For example, in a sales table, SalespersonID + ProductID + Month together should be unique (one record per salesperson per product per month), but no single column alone is unique.
To select multiple columns: click the first column, then hold Ctrl and click each additional column. Then right-click → Remove Duplicates.
= Table.Distinct(#"Previous Step", {"SalespersonID", "ProductID", "Month"})
Warning
When you remove duplicates on a subset of columns, Power Query keeps the first row it encounters for each unique key. This is fine if duplicates are truly identical in the other columns, but if they differ — like in our customer example where LastUpdated varies — you need to think carefully about which row you actually want to keep. The next section covers how to control that.
This is where beginners often run into trouble. You have duplicate customer records, and one is more recent, more complete, or more authoritative than the other. You can't just remove duplicates blindly — you need to keep the right one.
The technique is to sort first, then remove duplicates. Because Power Query's Table.Distinct always keeps the first occurrence, you can control which row that is by sorting the table before deduplication runs.
Scenario: You want to keep the most recently updated customer record.
Step 1 — Sort by LastUpdated descending:
LastUpdated column header= Table.Sort(#"Previous Step", {{"LastUpdated", Order.Descending}})
Now the most recent record for each customer appears first.
Step 2 — Remove duplicates on CustomerID:
CustomerID column= Table.Distinct(#"Sorted Rows", {"CustomerID"})
Because you sorted descending before deduplicating, the first occurrence Power Query encounters for each CustomerID is the most recent one — which is exactly what you wanted to keep.
Key insight
The combination of Sort → Remove Duplicates is one of the most important patterns in Power Query deduplication. It gives you full control over the "survivor" row without writing complex custom logic. Always ask yourself: "If I have two rows for the same key, which one should win?" — then sort so that winner appears first.
Sometimes you don't just want to remove the duplicates — you want to learn something from them before they're collapsed. For example: keep one row per customer, but calculate the total number of orders they placed, or keep only the maximum purchase amount across their duplicate rows.
This is where Grouping and Aggregating Data in Power Query becomes a deduplication tool.
Sample data — Sales Transactions:
| CustomerID | CustomerName | SaleAmount | SaleDate |
|---|---|---|---|
| C-001 | Sarah Chen | 1200.00 | 2024-01-15 |
| C-001 | Sarah Chen | 850.00 | 2024-02-20 |
| C-002 | Marcus Webb | 3400.00 | 2024-01-22 |
| C-003 | Priya Nair | 675.00 | 2024-03-05 |
| C-002 | Marcus Webb | 900.00 | 2024-03-10 |
You want one row per customer, with their total sales and number of transactions.
To use Group By:
CustomerIDCustomerName)TotalSales, Operation: Sum, Column: SaleAmountTransactionCount, Operation: Count Rows= Table.Group(
#"Previous Step",
{"CustomerID", "CustomerName"},
{
{"TotalSales", each List.Sum([SaleAmount]), type number},
{"TransactionCount", each Table.RowCount(_), type number}
}
)
Result:
| CustomerID | CustomerName | TotalSales | TransactionCount |
|---|---|---|---|
| C-001 | Sarah Chen | 2050.00 | 2 |
| C-002 | Marcus Webb | 4300.00 | 2 |
| C-003 | Priya Nair | 675.00 | 1 |
You've deduplicated to one row per customer and preserved meaningful information from the removed rows. This is far more powerful than a simple delete.
Tip
Group By is your best friend when you need to deduplicate a fact table (like transactions) into a summary table. If you only need the deduplication without aggregation, Sort → Remove Duplicates is simpler. But if collapsing duplicates means losing data you care about, Group By is the right tool.
Sometimes your requirement is the opposite of what we've been doing. Instead of "keep one copy of each duplicate," you want "remove any row that appears more than once entirely." This is useful in scenarios like identifying data entry errors — if a transaction ID was entered twice, you don't want either copy.
Power Query doesn't have a built-in button for this, but you can accomplish it with a Group By + filter approach.
Step 1 — Group By to count occurrences:
Group by all columns that define a duplicate, adding a count column:
= Table.Group(
#"Previous Step",
{"OrderID", "CustomerID", "Product"},
{{"OccurrenceCount", each Table.RowCount(_), type number}}
)
Step 2 — Filter to keep only rows with count = 1:
In Power Query Editor, click the dropdown on OccurrenceCount, uncheck everything except 1, click OK.
= Table.SelectRows(#"Grouped Rows", each [OccurrenceCount] = 1)
Step 3 — Remove the OccurrenceCount column:
You no longer need the helper column, so select it and delete it.
Now you have a table containing only rows that appeared exactly once in the source — every duplicated entry has been completely removed.
Warning
Be very intentional about when you use this approach. Removing all copies of duplicates instead of keeping one is a high-stakes decision. If your data has legitimate repeat entries (a customer genuinely placed the same order twice), you'd be deleting valid data. Always confirm the business rule before applying this pattern.
One of the most common deduplication scenarios arises after you've combined data from multiple sources using Combining Data from Multiple Sources with Append and Merge Queries. When you append tables, overlapping records create duplicates you need to clean up.
A common pattern:
AllRecords)AllRecords, add an index column if you need to track source orderThis gives you a deduplicated master list that intelligently picks the best record when sources conflict. For example, if your CRM data is more authoritative than your spreadsheet, sort so CRM rows come first, then dedup — CRM wins every tie.
Tip
When deduplicating after an append, consider adding a Source column to each table before appending (e.g., "CRM", "Spreadsheet", "Email Platform"). Then you can sort by source priority before deduplication, ensuring the most trusted source wins. This is a lightweight version of a data governance pattern called "source of truth prioritization."
Let's put this together with a realistic end-to-end exercise. You have a customer master file with duplicate records from a recent system migration.
Your dataset (type this into Excel or create a table):
| CustID | FullName | Region | AccountTier | LastActivity | |
|---|---|---|---|---|---|
| 1001 | Amara Osei | amara@demo.com | West | Gold | 2024-05-01 |
| 1002 | Ben Torres | ben@demo.com | East | Silver | 2024-03-15 |
| 1001 | Amara Osei | amara@demo.com | West | Platinum | 2024-08-20 |
| 1003 | Claire Kim | claire@demo.com | North | Gold | 2024-06-10 |
| 1002 | Ben Torres | ben@demo.com | East | Silver | 2024-03-15 |
| 1004 | Diego Ramos | diego@demo.com | South | Bronze | 2024-01-30 |
| 1003 | Claire Kim | claire@demo.com | North | Gold | 2024-07-22 |
Your task:
AccountTier and LastActivity values — you want the most recent recordLastActivitySteps:
After completing the exercise, examine the Applied Steps pane. You should see two steps: Sorted Rows and Removed Duplicates. Click back on Sorted Rows to verify the data was sorted correctly before deduplication ran — this is how you audit your own work.
Mistake 1: Removing duplicates before sorting
If you remove duplicates before sorting, Power Query will keep whatever row happens to appear first in the table's natural order — which may be the oldest, most incomplete, or least authoritative record. Always sort first when the choice of survivor matters.
Mistake 2: Deduplicating on the wrong column scope
If you right-click with no column selected and click Remove Duplicates from the table-level menu, you get full-row deduplication. If you want column-level deduplication, make sure you've selected the right column(s) first. Double-check the generated M code: Table.Distinct(Source) vs Table.Distinct(Source, {"ColumnName"}).
Mistake 3: Case sensitivity surprises
Power Query's default text comparisons are case-insensitive for many operations, but Table.Distinct can behave differently depending on your data source and locale settings. "SARAH CHEN" and "sarah chen" might or might not be considered duplicates. If you need consistent behavior, normalize your text to lowercase first using Cleaning Messy Data with Power Query: Nulls, Errors, and Type Conversions before deduplicating.
Mistake 4: Treating null values incorrectly
Two rows where your key column contains null will be treated as having the same key by Table.Distinct — meaning only one null row is kept. If nulls in your key column represent genuinely missing data (not "unknown entity"), you may want to filter those rows out separately before deduplication.
Mistake 5: Not checking your row count before and after
Always add a quick sanity check: note your row count before removing duplicates (shown in the bottom status bar of Power Query Editor), then check again after. If you expected to remove 50 duplicates and lost 500 rows, something went wrong with your column selection. You can also rename your steps descriptively — "SortByLastActivity" and "DeduplicateByCustomerID" — which is a good habit discussed in Power Query Best Practices for Maintainable ETL.
Tip
If you're unsure how many duplicates exist before you remove them, use Group By first to count occurrences per key. This gives you a preview of what's duplicated and how many copies exist — useful both for auditing data quality and for getting stakeholder approval before changing the dataset.
Deduplication is one of those skills that looks simple on the surface — "just click Remove Duplicates, right?" — but has real depth once you're working with real data where the right row to keep actually matters.
Here's what you've mastered in this lesson:
Deduplication rarely happens in isolation. It's usually one step in a larger cleaning pipeline — you're also handling nulls, fixing data types, standardizing text formats, and possibly merging data from multiple sources. As your queries get more complex, understanding how each step feeds into the next becomes critical. The article on Understanding Query Dependencies and Evaluation Order in Power Query will help you reason clearly about why step order matters — which is exactly the intuition you need to be confident about the Sort → Deduplicate pattern.
If you're working with data from multiple systems and deduplication is part of a larger data modeling challenge, take a look at Managing Many-to-Many Relationships in Power Query: Deduplication, Bridge Tables, and Safe Merge Strategies — it takes the deduplication techniques from this lesson and applies them in the context of building clean dimensional models.
Clean data is trustworthy data. And trustworthy data is the foundation of everything else you'll build.