7. Data Cleaning A–Z in Power Query
7.4 Removing Duplicates (Full Row and by Key)
Before
| Order ID | Product ID | Qty | Status |
|---|---|---|---|
| BLK-20001 | P-101 | 2 | Delivered |
| BLK-20001 | P-101 | 2 | Delivered |
| BLK-20002 | P-205 | 1 | Cancelled |
| BLK-20002 | P-205 | 1 | Delivered |
After (by key, latest status kept)
| Order ID | Product ID | Qty | Status |
|---|---|---|---|
| BLK-20001 | P-101 | 2 | Delivered |
| BLK-20002 | P-205 | 1 | Delivered |
Full-row duplicates. The first two rows are identical in every column. This happens when the same file is exported twice.
Duplicates by key. The last two rows have the same Order ID + Product ID (the key of an order line) but a different status. The file captured the line before and after the status changed.
Steps in Power BI
- Full row: click the table icon at the top-left corner of the data grid (or press Ctrl + A in the grid) to select all columns › Home › Remove Rows › Remove Duplicates.
- By key: hold Ctrl and select only Order ID and Product ID › Home › Remove Rows › Remove Duplicates.
- To keep the latest status, first sort by Status Updated DateTime descending, add Table.Buffer (see below), then remove duplicates by key.
- To inspect duplicates instead of deleting them, select the key columns › Home › Keep Rows › Keep Duplicates.
FullRowDistinct = Table.Distinct(Source),
ByKey = Table.Distinct(Source, {"Order ID", "Product ID"}),
// keep the latest version of each order line
Sorted = Table.Buffer(Table.Sort(Source, {{"Status Updated DateTime", Order.Descending}})),
Latest = Table.Distinct(Sorted, {"Order ID", "Product ID"})
Ravindra Bagale's Tip
Friends, don't make a mistake here: Remove Duplicates is case-sensitive and space-sensitive: "BLK-20001" and "blk-20001 " are different to Power Query. Always trim and fix the case first. In the Orders table, never remove duplicates on Order ID alone, because one order has many lines; the real key is Order ID + Product ID. Never forget this.
Ravindra Bagale's Tip – मराठी
मित्रांनो, इथे चूक करू नका: Remove Duplicates case आणि spaces दोन्हींबाबत sensitive आहे: Power Query साठी "BLK-20001" आणि "blk-20001 " वेगळे आहेत. नेहमी आधी trim करा आणि case दुरुस्त करा. Orders table मध्ये फक्त Order ID वर कधीच duplicates काढू नका, कारण एका order मध्ये अनेक lines असतात; खरी key म्हणजे Order ID + Product ID. हे अजिबात विसरू नका.
Ravindra Bagale's Tip – हिंदी
दोस्तों, यहाँ गलती मत करना: Remove Duplicates case और spaces दोनों के प्रति sensitive है: Power Query के लिए "BLK-20001" और "blk-20001 " अलग हैं. हमेशा पहले trim करो और case ठीक करो. Orders table में सिर्फ़ Order ID पर कभी duplicates मत हटाओ, क्योंकि एक order में कई lines होती हैं; असली key है Order ID + Product ID. यह बिल्कुल मत भूलना.
Practice task
The Amazon Now export for Solapur (Hotgi Road) was downloaded twice and appended. Use Keep Duplicates to count the repeated order lines, then remove them.